用于根据序列对结果进行分组的 SQL 查询 [英] SQL Query for Grouping the results based on sequence

问题描述

我有一张这样的桌子:

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

我想像这样将连续的序列号分组为一行:

I want to group the continuous sequence numbers into a single row like this:

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
;

这篇关于用于根据序列对结果进行分组的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！