--查找断号区间
--建立測試環境
Create Table TEST
(ID Int)
--插入數據
Insert TEST Select 1
Union All Select 2
Union All Select 5
Union All Select 6
Union All Select 8
Union All Select 9
Union All Select 10
Union All Select 11
Union All Select 16
GO
--測試
Select
Rtrim(A.ID) + '-' + Rtrim(Min(B.ID)) As 断号区间
From
(Select
T.ID + 1 As ID
From
TEST T
Where Not Exists(Select ID From TEST Where ID = T.ID + 1)) A
Inner Join
(Select
T.ID - 1 As ID
From
TEST T
Where Not Exists(Select ID From TEST Where ID = T.ID - 1)) B
On A.ID <= B.ID
Group By A.ID
GO
--刪除測試環境
Drop Table TEST
--結果
/*
断号区间
3-4
7-7
12-15
*/