SqlServer中连续号及断号查询—附源码

效果如下图所示：  

SqlServer中连续号及断号查询SQL如下：

--1.定义临时表
DECLARE @TestTemp TABLE(
		TestCode NVARCHAR(50),
		TestNum INT 
)

DECLARE @DataTemp TABLE(
		TestCode NVARCHAR(50),
		TestNumStr NVARCHAR(100)
)

--2.插入测试数据
INSERT INTO @TestTemp(TestCode,TestNum)
VALUES
('Code001',2),('Code001',3),('Code001',4),('Code001',6),('Code001',8),('Code001',9),
('Code002',20),('Code002',15),('Code002',16),('Code002',17),('Code002',10),('Code002',21)

--3.连续号处理后插入临时表
INSERT INTO @DataTemp(TestCode,TestNumStr)
SELECT TestCode,
	   (CASE WHEN MAX(b.TestNum)>MIN(b.TestNum) 
		    THEN CAST(MIN(b.TestNum) AS NVARCHAR(10))+'~'+CAST(MAX(b.TestNum) AS NVARCHAR(10)) 
			ELSE CAST(MIN(b.TestNum) AS NVARCHAR(10)) 
	   END) AS TestNumStr
FROM (
        select a.*,(a.TestNum-ROW_NUMBER() OVER(ORDER BY a.TestCode)) cc
        from (
               SELECT TOP 100 t.TestCode,t.TestNum 
			   FROM @TestTemp AS t
			   ORDER BY t.TestCode,t.TestNum
        ) a
)  b
group by b.TestCode,b.cc

--4.查询最终结果
SELECT * FROM @DataTemp

--5.查询最终结果:按照Code分组,连续号列转行
SELECT TestCode,TestNumStr=STUFF((SELECT ','+tt.TestNumStr FROM @DataTemp AS tt WHERE tt.TestCode=t.TestCode FOR XML PATH('')),1,1,'')
FROM @DataTemp AS t
GROUP BY TestCode

--6.断号查询 方式1
SELECT * FROM @TestTemp AS a 
WHERE NOT EXISTS(SELECT * FROM @TestTemp b WHERE b.TestNum = a.TestNum + 1)
	  AND TestNum < (SELECT MAX(TestNum) FROM @TestTemp) 

--7.断号查询  方式2
SELECT a.TestCode,a.TestNum+1 AS TestNum
FROM @TestTemp AS a 
WHERE NOT EXISTS(SELECT * FROM @TestTemp b WHERE b.TestNum = a.TestNum + 1)
	  AND TestNum < (SELECT MAX(TestNum) FROM @TestTemp) 
ORDER BY a.TestCode,a.TestNum