我试图计算运行总计.但是当累积和大于另一个列值时,它应该重置
create table #reset_runn_total ( id int identity(1,1),val int,reset_val int ) insert into #reset_runn_total values (1,10),(8,12),(6,14),(5,13),(3,11),(9,8),(10,12)
样本数据
+----+-----+-----------+ | id | val | reset_val | +----+-----+-----------+ | 1 | 1 | 10 | | 2 | 8 | 12 | | 3 | 6 | 14 | | 4 | 5 | 10 | | 5 | 6 | 13 | | 6 | 3 | 11 | | 7 | 9 | 8 | | 8 | 10 | 12 | +----+-----+-----------+
预期结果
+----+-----+-----------------+-------------+ | id | val | reset_val | Running_tot | +----+-----+-----------------+-------------+ | 1 | 1 | 10 | 1 | | 2 | 8 | 12 | 9 | --1+8 | 3 | 6 | 14 | 15 | --1+8+6 -- greater than reset val | 4 | 5 | 10 | 5 | --reset | 5 | 6 | 13 | 11 | --5+6 | 6 | 3 | 11 | 14 | --5+6+3 -- greater than reset val | 7 | 9 | 8 | 9 | --reset -- greater than reset val | 8 | 10 | 12 | 10 | --reset +----+-----+-----------------+-------------+
查询：
;WITH cte
AS (SELECT id,val,reset_val,val AS running_total
FROM #reset_runn_total
WHERE id = 1
UNION ALL
SELECT r.*,CASE
WHEN lag(c.running_total + r.val) over(order by r.id) > lag(r.reset_val) over(order by r.id) THEN r.reset_val
ELSE c.running_total + r.val
END
FROM cte c
JOIN #reset_runn_total r
ON r.id = c.id + 1)
SELECT *
FROM cte
明显滞后不会得到以前的价值观呢？
解决方法
尝试标记上一行
WITH cte
AS (SELECT id,val AS running_total,CASE WHEN val > reset_val THEN 1 ELSE 0 END as flag
FROM #reset_runn_total
WHERE id = 1
UNION ALL
SELECT r.*,CASE c.flag
WHEN 1 then r.val
ELSE c.running_total + r.val
END,CASE WHEN CASE c.flag
WHEN 1 then r.val
ELSE c.running_total + r.val
END > r.reset_val
THEN 1 ELSE 0 END
FROM cte c
JOIN #reset_runn_total r
ON r.id = c.id + 1)
SELECT *
FROM cte
