sql server中每行和每列的组合累积

Question

我想在我的表中添加一列，例如用于计算组合累积:

a    b    c    d        newcoulmn
50   0    0    0            50        
0    7    0    0            57
0    0    6    0            63
0    0    0   (4)           59
0    3    0    0            62
0    2    0    0            64
0    0    0   (8)           56
0    0    0   (7)           49

Answer 1

您需要选择a + b + c - d的和，然后使用窗口函数ROW_NUMBER()计算该值的累积和:

WITH CTE
AS
(
  SELECT *, a + b + c - d AS abcd,
     ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS rn
  FROM test
)
SELECT c1.a, c1.b, c1.c, c1.d, 
  COALESCE((SELECT SUM(c2.abcd)
            FROM cte AS c2
            WHERE c1.rn >= c2.rn), c1.abcd) AS newcolumn
FROM CTE AS c1;

在这里看到它的作用:

• SQL Fiddle Demo

这将给你:

|  A | B | C | D | NEWCOLUMN |
------------------------------
| 50 | 0 | 0 | 0 |        50 |
|  0 | 7 | 0 | 0 |        57 |
|  0 | 0 | 6 | 0 |        63 |
|  0 | 0 | 0 | 4 |        59 |
|  0 | 3 | 0 | 0 |        62 |
|  0 | 2 | 0 | 0 |        64 |
|  0 | 0 | 0 | 8 |        56 |
|  0 | 0 | 0 | 7 |        49 |

---

更新:

对于你的查询，可以这样写:

WITH YourOriginalQuery
AS
(
    select plitno, A, datils, 0 AS B, 0 AS C, 0 AS D from table1 
    union all 
    select critno, 0, 0, B, 0, 0 from table2 
    union all 
    select klitno, 0, 0, 0, C, 0 from table3 
    union all 
    select bgitno, 0, 0, 0, 0, D from table4
), WITH CTE
AS
(
  SELECT *, a + b + c - d AS abcd,
     ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS rn
  FROM YourOriginalQuery
)
SELECT c1.a, c1.b, c1.c, c1.d, 
  COALESCE((SELECT SUM(c2.abcd)
            FROM cte AS c2
            WHERE c1.rn >= c2.rn), c1.abcd) AS newcolumn
FROM CTE AS c1;