查找断号区间

--查找断号区间

--建立測試環境
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
*/