sql – 根据另一列重置运行总计

发布时间：2020-12-12 08:47:58 所属栏目：MsSql教程来源：网络整理

导读：我试图计算运行总计.但是当累积和大于另一个列值时,它应该重置 create table #reset_runn_total(id int identity(1,1),val int,reset_val int)insert into #reset_runn_totalvalues (1,10),(8,12),(6,14),(5,13),(3,11),(9,8),(10,12) 样本数据 +----+-----+-

我试图计算运行总计.但是当累积和大于另一个列值时,它应该重置

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!