SQL 查詢連續號碼段：經典「差值分組法」與 ROW_NUMBER 現代寫法對比

問題：找出連續號碼區間

資料：

FPHM  KSHM
2014  00000001
2014  00000002
2014  00000003
2014  00000004
2014  00000005
2014  00000007  -- 斷點
2014  00000008
2014  00000009
2013  00000120
2013  00000121
2013  00000122
2013  00000124  -- 斷點
2013  00000125

預期結果：

FPHM  Start_KSHM  End_KSHM
2014  00000001    00000005
2014  00000007    00000009
2013  00000120    00000122
2013  00000124    00000125

核心原理：連續數列「值 - 序號」為常數

• 連續數列：1,2,3,4,5 → 序號 1,2,3,4,5 → 差值 0,0,0,0,0
• 斷點後：7,8,9 → 序號 6,7,8 → 差值 1,1,1
• 同一分組鍵 (FPHM) 下，差值相同 = 同一連續區段

寫法一：經典差值分組法 (Oracle/通用 SQL)

SELECT FPHM,
       MIN(KSHM) AS Start_KSHM,
       MAX(KSHM) AS End_KSHM
FROM (
    SELECT FPHM, KSHM,
           CAST(KSHM AS BIGINT) - ROW_NUMBER() OVER (PARTITION BY FPHM ORDER BY KSHM) AS GrpKey
    FROM T
) A
GROUP BY FPHM, GrpKey
ORDER BY FPHM, Start_KSHM;

寫法二：LAG/LEAD 窗口函數法 (SQL 2012+)

WITH Flagged AS (
    SELECT FPHM, KSHM,
           CASE WHEN CAST(KSHM AS BIGINT) - LAG(CAST(KSHM AS BIGINT)) OVER (PARTITION BY FPHM ORDER BY KSHM) = 1
                THEN 0 ELSE 1 END AS IsNewGroup
    FROM T
),
Grouped AS (
    SELECT FPHM, KSHM,
           SUM(IsNewGroup) OVER (PARTITION BY FPHM ORDER BY KSHM ROWS UNBOUNDED PRECEDING) AS GrpID
    FROM Flagged
)
SELECT FPHM, MIN(KSHM) AS Start_KSHM, MAX(KSHM) AS End_KSHM
FROM Grouped
GROUP BY FPHM, GrpID
ORDER BY FPHM, Start_KSHM;

寫法三：遞歸 CTE 法 (教學用，效能較差)

WITH Rec AS (
    SELECT FPHM, KSHM, KSHM AS Start_KSHM, 1 AS Lvl
    FROM T
    WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.FPHM = T.FPHM AND CAST(t2.KSHM AS BIGINT) = CAST(T.KSHM AS BIGINT) - 1)
    UNION ALL
    SELECT t.FPHM, t.KSHM, r.Start_KSHM, r.Lvl + 1
    FROM T t
    JOIN Rec r ON t.FPHM = r.FPHM AND CAST(t.KSHM AS BIGINT) = CAST(r.KSHM AS BIGINT) + 1
)
SELECT FPHM, Start_KSHM, MAX(KSHM) AS End_KSHM
FROM Rec
GROUP BY FPHM, Start_KSHM
ORDER BY FPHM, Start_KSHM
OPTION (MAXRECURSION 0);

效能與適用性

關鍵細節

1. KSHM 為字串需 CAST(... AS BIGINT) 運算
2. 分組鍵必須包含 FPHM（發票號碼），跨組別不連續
3. 結果可直接用於報表、對帳、缺號偵測