在SQL Server中按顺序分组

小编典典

在SQL Server中按顺序分组

sql

对于此表：

+----+--------+-------+
| ID | Status | Value |
+----+--------+-------+
|  1 |      1 |     4 |
|  2 |      1 |     7 |
|  3 |      1 |     9 |
|  4 |      2 |     1 |
|  5 |      2 |     7 |
|  6 |      1 |     8 |
|  7 |      1 |     9 |
|  8 |      2 |     1 |
|  9 |      0 |     4 |
| 10 |      0 |     3 |
| 11 |      0 |     8 |
| 12 |      1 |     9 |
| 13 |      3 |     1 |
+----+--------+-------+

我需要将具有相同顺序的组相加，Status以产生此结果。

+--------+------------+
| Status | Sum(Value) |
+--------+------------+
|      1 |         20 |
|      2 |          8 |
|      1 |         17 |
|      2 |          1 |
|      0 |         15 |
|      1 |          9 |
|      3 |          1 |
+--------+------------+

如何在SQL Server中做到这一点？

注意：该ID列中的值是连续的。

阅读 222

2021-04-07

共1个答案

小编典典

根据我在您的问题中添加的标签，这是一个空白和孤岛的问题。

效果最好的解决方案可能是

WITH T
     AS (SELECT *,
                ID - ROW_NUMBER() OVER (PARTITION BY [STATUS] ORDER BY [ID]) AS Grp
         FROM   YourTable)
SELECT [STATUS],
       SUM([VALUE]) AS [SUM(VALUE)]
FROM   T
GROUP  BY [STATUS],
          Grp
ORDER  BY MIN(ID)

如果ID值不能保证如所述连续，那么您将需要使用

ROW_NUMBER() OVER (ORDER BY [ID]) - 
       ROW_NUMBER() OVER (PARTITION BY [STATUS] ORDER BY [ID]) AS Grp

而是在CTE定义中。

SQL小提琴

2021-04-07