資料庫中常需找出連續號碼區間(如發票號、流水號)。當資料中存在斷點時,如何用單一 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
連續數列的特性:號碼值 - 行號 = 常數。利用此特性分組:
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
ROW_NUMBER() 產生連續遞增序號(1,2,3...)KSHM - ROW_NUMBER():連續段落中此差值固定;遇斷點差值改變FPHM(年份)與差值 grp 分組,取 MIN/MAX 即得區間TO_NUMBER(KSHM) - ROW_NUMBER() OVER (...)CAST(KSHM AS INT) - ROW_NUMBER() OVER (...)KSHM::INT - ROW_NUMBER() OVER (...)「值減行號」分組法是解決連續區間查詢的標準模式,效能優異且語法簡潔。掌握此技巧,即可輕鬆處理發票斷號、缺號偵測、連續登入天數等各類連續性分析需求。