萬盛學電腦網

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

SQL 查詢連續號碼段(Gap-and-Islands)經典實作

資料庫中常需找出連續號碼區間(如發票號、流水號)。當資料中存在斷點時,如何用單一 SQL 找出所有連續區間的起迄號?這是經典的「Gap-and-Islands」問題。

範例資料

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

核心邏輯:ROW_NUMBER 差值分組法

連續數列的特性:號碼值 - 行號 = 常數。利用此特性分組:

SELECT b.FPHM,
       MIN(b.KSHM) AS Start_HM,
       MAX(b.KSHM) AS End_HM
FROM (
    SELECT a.*,
           TO_NUMBER(a.KSHM) - ROW_NUMBER() OVER (PARTITION BY a.FPHM ORDER BY a.KSHM) AS grp
    FROM (
        SELECT * FROM t ORDER BY FPHM, KSHM
    ) a
) b
GROUP BY b.FPHM, b.grp
ORDER BY b.FPHM, Start_HM;

查詢結果

FPHM | START_HM  | END_HM
-----|-----------|---------
2013 | 00000120  | 00000122
2013 | 00000124  | 00000125
2014 | 00000001  | 00000005
2014 | 00000007  | 00000009

原理解析

  1. ROW_NUMBER() 產生連續遞增序號(1,2,3...)
  2. KSHM - ROW_NUMBER():連續段落中此差值固定;遇斷點差值改變
  3. 以 FPHM(年份)與差值 grp 分組,取 MIN/MAX 即得區間

跨資料庫寫法備註

  • Oracle:TO_NUMBER(KSHM) - ROW_NUMBER() OVER (...)
  • SQL Server:CAST(KSHM AS INT) - ROW_NUMBER() OVER (...)
  • PostgreSQL/MySQL 8+:KSHM::INT - ROW_NUMBER() OVER (...)

總結

「值減行號」分組法是解決連續區間查詢的標準模式,效能優異且語法簡潔。掌握此技巧,即可輕鬆處理發票斷號、缺號偵測、連續登入天數等各類連續性分析需求。

copyright © 萬盛學電腦網 all rights reserved