我有这样的表：
ID Seq Amt 1 1 500 1 2 500 1 3 500 1 5 500 2 10 600 2 11 600 3 1 700 3 3 700
我想将连续序列号分组成一行,如下所示：
ID Start End TotalAmt 1 1 3 1500 1 5 5 500 2 10 11 1200 3 1 1 700 3 3 3 700
请帮助实现这一结果.
解决方法
WITH numbered AS (
SELECT
ID,Seq,Amt,SeqGroup = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Seq) - Seq
FROM atable
)
SELECT
ID,Start = MIN(Seq),[End] = MAX(Seq),TotalAmt = SUM(Amt)
FROM numbered
GROUP BY ID,SeqGroup
ORDER BY ID,Start
;
