給定發票號碼表 (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
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;
| Kshm | ROW_NUMBER | Kshm - RN | 分組 |
|---|---|---|---|
| 1 | 1 | 0 | Group 0 |
| 2 | 2 | 0 | |
| 3 | 3 | 0 | |
| 4 | 4 | 0 | |
| 5 | 5 | 0 | |
| 7 | 6 | 1 | Group 1 |
| 8 | 7 | 1 | |
| 9 | 8 | 1 |
連續數列中 值 - 序號 恆為常數;斷點導致差值變化,自然分組。
CAST(Kshm AS BIGINT) - ROW_NUMBER() OVER (...) AS GrpKey
轉數值運算後再分組,最終 MIN/MAX(Kshm) 保留原始字串格式。
DATEDIFF(DAY, '19000101', DateCol) - RN)。-- 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);