断号查询（查找不连续ID记录）
需求：找出某列断流的ID号.
例如：1，2，7，8，9. 断流的ID是 3 至 6.
直接上代码算了：
不分组，单列：
IF object_ID('tempdb..#Tab') IS NOT NULL
DROP TABLE #Tab
CREATE TABLE #Tab(ID INT)
GO
INSERT #Tab(ID)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 10 UNION ALL
SELECT 12 UNION ALL
SELECT 15 UNION ALL
SELECT 20 UNION ALL
SELECT 21
GO
-- 某一区间连续ID的最大值 + 1
;WITH CTE_A AS
(
SELECT DISTINCT RN = DENSE_RANK() OVER (ORDER BY a.ID)
,ID = a.ID + 1
FROM #Tab a
WHERE NOT EXISTS(SELECT * FROM #Tab WHERE ID = a.ID + 1)
)
-- 某一区间连续ID的最小值 - 1
,CTE_B AS
(
SELECT DISTINCT RN = DENSE_RANK() OVER (ORDER BY a.ID)
,ID = a.ID - 1
FROM #Tab a
WHERE NOT EXISTS(SELECT * FROM #Tab WHERE ID = a.ID - 1)
)
SELECT CASE
WHEN A.ID = B.ID THEN LTRIM(a.ID)
ELSE LTRIM(a.ID) + ' - ' + LTRIM(b.ID)
END ID
FROM CTE_A a
INNER JOIN CTE_B b
ON a.RN = b.RN - 1
/*
结果:
ID
---------------------------
4 - 5
8 - 9
11
13 - 14
16 - 19
*/
IF object_ID('tempdb..#Tab') IS NOT NULL
DROP TABLE #Tab
CREATE TABLE #Tab(Sort NVARCHAR(200),ID INT)
GO
INSERT #Tab(Sort,ID)
SELECT N'a', 1 UNION ALL
SELECT N'a', 2 UNION ALL
SELECT N'a', 3 UNION ALL
SELECT N'a', 6 UNION ALL
SELECT N'b', 1 UNION ALL
SELECT N'b', 2 UNION ALL
SELECT N'b', 6 UNION ALL
SELECT N'b', 7 UNION ALL
SELECT N'b', 12 UNION ALL
SELECT N'b', 14
GO
-- 某一区间连续ID的最大值 + 1
;WITH CTE_A AS
(
SELECT DISTINCT
RN = DENSE_RANK() OVER (ORDER BY Sort,a.ID)
,Sort
,ID = a.ID + 1
FROM #Tab a
WHERE NOT EXISTS(SELECT * FROM #Tab WHERE SOrt = a.Sort AND ID = a.ID + 1)
)
-- 某一区间连续ID的最小值 - 1
,CTE_B AS
(
SELECT DISTINCT
RN = DENSE_RANK() OVER (ORDER BY Sort,a.ID)
,Sort
,ID = a.ID - 1
FROM #Tab a
WHERE NOT EXISTS(SELECT * FROM #Tab WHERE SOrt = a.Sort AND ID = a.ID - 1)
)
SELECT
a.Sort
,CASE
WHEN A.ID = B.ID THEN LTRIM(a.ID)
ELSE LTRIM(a.ID) + ' - ' + LTRIM(b.ID)
END ID
FROM CTE_A a
INNER JOIN CTE_B b
ON a.Sort = b.Sort AND a.RN = b.RN - 1
/*
结果:
Sort ID
------------------------- ---------------------------
a 4 - 5
b 3 - 5
b 8 - 11
b 13
*/