萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL 查詢連續號碼段技巧:ROW_NUMBER 差值分組法

間隙與島嶼問題:高效識別連續號碼區間

給定發票號碼表 (Fphm, Kshm),Kshm 可能連續也可能斷點。需查詢每個 Fphm 下的連續區間起訖號。

測試資料

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

核心解法:ROW_NUMBER 差值不變性

WITH Ordered AS (
    SELECT Fphm, Kshm,
           ROW_NUMBER() OVER (PARTITION BY Fphm ORDER BY Kshm) AS rn
    FROM Invoices
),
Grouped AS (
    SELECT Fphm, Kshm,
           CAST(Kshm AS BIGINT) - rn AS GrpKey  -- 連續區間此值恆定
    FROM Ordered
)
SELECT Fphm, 
       MIN(Kshm) AS Start_Kshm, 
       MAX(Kshm) AS End_Kshm
FROM Grouped
GROUP BY Fphm, GrpKey
ORDER BY Fphm, Start_Kshm;

原理圖解

KshmROW_NUMBERKshm - RN分組
110Group 0
220
330
440
550
761Group 1
871
981

連續數列中 值 - 序號 恆為常數;斷點導致差值變化,自然分組。

擴展:支援字串型號碼(含前導零)

CAST(Kshm AS BIGINT) - ROW_NUMBER() OVER (...) AS GrpKey

轉數值運算後再分組,最終 MIN/MAX(Kshm) 保留原始字串格式。

效能優勢

  • 單次掃描,無自我連接,無游標,O(N log N)(排序成本)。
  • 適用百萬行級資料,秒級回應。
  • 可擴展至日期連續區間(DATEDIFF(DAY, '19000101', DateCol) - RN)。

Oracle/PostgreSQL 相容寫法

-- Oracle 使用 ROWNUM 需子查詢排序
SELECT Fphm, MIN(Kshm), MAX(Kshm)
FROM (
    SELECT Fphm, Kshm, TO_NUMBER(Kshm) - ROW_NUMBER() OVER (PARTITION BY Fphm ORDER BY Kshm) AS Grp
    FROM Invoices
)
GROUP BY Fphm, Grp
ORDER BY Fphm, MIN(Kshm);
copyright © 萬盛學電腦網 all rights reserved