先例行值SQL的值降低总和

Question

我有一个看起来像这样的表：

+---------+--------+-------+
|categ    |decrea  |lost   |
|---------|--------|-------+
|A        |1500    |20     |
|B        |1500    |40     |
|C        |1500    |60     |
+---------+--------+-------+

我想更改降低值，以使每个值等于减去累积损失。因此结果看起来像：

+---------+---------------+-------+
|categ    |decrea         |lost   |
|---------|---------------|-------+
|A        |1500-20        |20     |
|B        |1500-(20+40)   |40     |
|C        |1500-(20+40+60)|60     |
+---------+--------+------+-------+

anay帮助？

Answer 1

使用以下相关查询

DECLARE @Table TABLE(categ varchar(1),decra INT, lost int)
INSERT INTO @Table VALUES('A',1500,20)
INSERT INTO @Table VALUES('B',1500,40)
INSERT INTO @Table VALUES('C',1500,60)
SELECT *,    
    decra -
        (SELECT SUM(t2.lost) FROM @Table t2 WHERE t2.categ <= t1.categ) AS decra,
    lost
FROM @Table t1;

Answer 2

尝试SQL Server。

DECLARE @Table TABLE(categ varchar(1),decra INT, lost int)
INSERT INTO @Table VALUES('A',1500,20)
INSERT INTO @Table VALUES('B',1500,40)
INSERT INTO @Table VALUES('C',1500,60)
SELECT *
    ,decra-SUM(lost) OVER(ORDER BY categ) Accumulated
FROM @Table

输出：

categ   decra   lost    Accumulated
A       1500    20      1480
B       1500    40      1440
C       1500    60      1380

Answer 3

尝试使用自我加入方法：

SELECT
    t1.categ,
    t1.decrea - SUM(t2.lost) AS decrea,
    t1.lost
FROM yourTable t1
INNER JOIN yourTable t2
    ON t1.categ >= t2.categ
GROUP BY
    t1.categ, t1.decrea, t1.lost
ORDER BY
    t1.categ;
Results:
  categ decrea lost
1 A     1480   20
2 B     1440   40
3 C     1380   60

demo