斷號查詢（查找不連續ID記錄）

来源：互联网发布：java post与get 方式编辑：程序博客网时间：2024/06/17 13:15

需求：找出某列斷流的ID號.

例如：1，2，7，8，9. 斷流的ID是 3 至 6.

直接上代碼算了：

不分組，單列：

IF object_ID('tempdb..#Tab') IS NOT NULLDROP TABLE #TabCREATE TABLE #Tab(ID INT)GOINSERT #Tab(ID)SELECT 1 UNION ALLSELECT 2 UNION ALLSELECT 3 UNION ALLSELECT 6 UNION ALLSELECT 7 UNION ALLSELECT 10 UNION ALLSELECT 12 UNION ALLSELECT 15 UNION ALLSELECT 20 UNION ALLSELECT 21GO-- 某一區間連續ID的最大值 + 1;WITH CTE_A AS(SELECT DISTINCT RN = DENSE_RANK() OVER (ORDER BY a.ID)      ,ID = a.ID + 1FROM #Tab aWHERE 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 - 1FROM #Tab aWHERE 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 IDFROM CTE_A aINNER JOIN CTE_B bON a.RN = b.RN - 1/*結果:ID---------------------------4 - 58 - 91113 - 1416 - 19*/

先分組：

IF object_ID('tempdb..#Tab') IS NOT NULLDROP TABLE #TabCREATE TABLE #Tab(Sort NVARCHAR(200),ID INT)GOINSERT #Tab(Sort,ID)SELECT N'a', 1 UNION ALLSELECT N'a', 2 UNION ALLSELECT N'a', 3 UNION ALLSELECT N'a', 6 UNION ALLSELECT N'b', 1 UNION ALLSELECT N'b', 2 UNION ALLSELECT N'b', 6 UNION ALLSELECT N'b', 7 UNION ALLSELECT N'b', 12 UNION ALLSELECT N'b', 14GO-- 某一區間連續ID的最大值 + 1;WITH CTE_A AS(SELECT DISTINCT RN = DENSE_RANK() OVER (ORDER BY Sort,a.ID),Sort,ID = a.ID + 1FROM #Tab aWHERE 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 - 1FROM #Tab aWHERE NOT EXISTS(SELECT * FROM #Tab WHERE SOrt = a.Sort AND ID = a.ID - 1))SELECT a.Sort,CASEWHEN A.ID = B.ID THEN LTRIM(a.ID) ELSE LTRIM(a.ID) + ' - ' + LTRIM(b.ID) END IDFROM CTE_A aINNER JOIN CTE_B bON a.Sort = b.Sort AND a.RN = b.RN - 1/*結果:Sort                       ID------------------------- ---------------------------a                         4 - 5b                         3 - 5b                         8 - 11b                         13*/

0 0