编号处理这个在日常的工作中中也是比较常见，这里做下备忘。
-- ========================================================================================== --简单情况 -- ========================================================================================== DECLARE @t TABLE(serialNum INT) INSERT INTO @t SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 9 -- ========================================================================================== --是否存在缺号,当前编号不存在下一个编号或是不存在上一个编号则表明存在缺号 -- ========================================================================================== SELECT * FROM @t a WHERE NOT EXISTS(SELECT 1 FROM @t WHERE serialNum=a.serialNum+1) SELECT * FROM @t a WHERE NOT EXISTS(SELECT 1 FROM @t WHERE serialNum=a.serialNum-1) --存在下面情况的记录也表明存在缺号【表述太绕口不在表述】 SELECT * FROM @t a WHERE serialNum>(SELECT MAX(serialNum)+1 FROM @t WHERE serialNum<a.serialNum) -- ========================================================================================== ----缺号分布情况 -- ========================================================================================== SELECT startNum=(SELECT MAX(serialNum)+1 FROM @t b WHERE serialNum<a.serialNum) ,endNum=serialNum-1 FROM @t a WHERE serialNum>(SELECT MAX(serialNum)+1 FROM @t WHERE serialNum<a.serialNum) SELECT startNum=(SELECT MAX(serialNum)+1 FROM @t b WHERE serialNum<a.serialNum
AND NOT EXISTS(SELECT 1 FROM @t WHERE serialNum=b.serialNum+1)) ,endNum=serialNum-1 FROM @t a WHERE serialNum>(SELECT MAX(serialNum)+1 FROM @t WHERE serialNum<a.serialNum) -- ========================================================================================== ----已用编号分布 -- ========================================================================================== SELECT startNum=serialNum, endNum=(SELECT min(serialNum) FROM @t c WHERE serialNum>=a.serialNum
AND NOT EXISTS(SELECT 1 FROM @t WHERE serialNum=c.serialNum+1)) FROM @t a WHERE NOT EXISTS(SELECT 1 FROM @t WHERE serialNum=a.serialNum-1) -- ========================================================================================== --复杂情况 -- ========================================================================================== DECLARE @t TABLE(MType VARCHAR(20), serialNum INT) INSERT INTO @t SELECT 'A',1 UNION ALL SELECT 'A',2 UNION ALL SELECT 'A',5 UNION ALL SELECT 'B',6 UNION ALL SELECT 'B',9 UNION ALL SELECT 'B',12 UNION ALL SELECT 'B',13 -- ========================================================================================== --是否存在缺号,当前编号不存在下一个编号或是不存在上一个编号则表明存在缺号 -- ========================================================================================== SELECT * FROM @t a WHERE NOT EXISTS(SELECT 1 FROM @t WHERE MType=a.MType AND serialNum=a.serialNum+1) SELECT * FROM @t a WHERE NOT EXISTS(SELECT 1 FROM @t WHERE MType=a.MType AND serialNum=a.serialNum-1) --存在下面情况的记录也表明存在缺号【表述太绕口不在表述】 SELECT * FROM @t a WHERE serialNum>(SELECT MAX(serialNum)+1 FROM @t WHERE MType=a.MType AND serialNum<a.serialNum) -- ========================================================================================== ----缺号分布情况 -- ========================================================================================== SELECT MType, startNum=(SELECT MAX(serialNum)+1 FROM @t b WHERE MType=a.MType AND serialNum<a.serialNum) ,endNum=serialNum-1 FROM @t a WHERE serialNum>(SELECT MAX(serialNum)+1 FROM @t WHERE MType=a.MType AND serialNum<a.serialNum) SELECT MType, startNum=(SELECT MAX(serialNum)+1 FROM @t b WHERE MType=a.MType AND serialNum<a.serialNum
AND NOT EXISTS(SELECT 1 FROM @t WHERE MType=b.MType AND serialNum=b.serialNum+1)) ,endNum=serialNum-1 FROM @t a WHERE serialNum>
(SELECT MAX(serialNum)+1 FROM @t WHERE MType=a.MType AND serialNum<a.serialNum) -- ========================================================================================== ----已用编号分布 -- ========================================================================================== SELECT MType, startNum=serialNum, endNum=(SELECT min(serialNum) FROM @t c WHERE MType=a.MType AND serialNum>=a.serialNum
AND NOT EXISTS(SELECT 1 FROM @t WHERE MType=c.MType and serialNum=c.serialNum+1)) FROM @t a WHERE NOT EXISTS(SELECT 1 FROM @t WHERE MType=a.MType AND serialNum=a.serialNum-1) CREATE TABLE #t(serialNum INT) INSERT INTO #t SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 9 SELECT * FROM #t --是否存在缺号 SELECT * FROM #t a WHERE NOT EXISTS(SELECT 1 FROM #t WHERE serialNum=a.serialNum-1)--不存在前一编号 SELECT * FROM #t a WHERE NOT EXISTS(SELECT 1 FROM #t WHERE serialNum=a.serialNum+1)--不存在后一编号 SELECT * FROM #T a WHERE serialNum>(SELECT MAX(serialNum)+1 FROM #t WHERE serialNum<a.serialNum) --自己理解 --缺号分布情况,不存在之后的编号，说明当前编号是一段连续编号的结束编号即为缺失编号的起始编号 --A SELECT STARTN=serialNum+1, ENDN=(SELECT MIN(serialNum) FROM #t b WHERE serialNum>a.serialNum AND NOT EXISTS(SELECT 1 FROM #t WHERE serialNum=b.serialNum-1))-1 FROM #t a WHERE NOT EXISTS(SELECT 1 FROM #t WHERE serialNum=a.serialNum+1) --B SELECT STARTN=(SELECT MIN(serialNum) FROM #t b WHERE serialNum>a.serialNum AND NOT EXISTS(SELECT 1 FROM #t WHERE serialNum=b.serialNum+1))+1, ENDN=(SELECT MIN(serialNum) FROM #t b WHERE serialNum>a.serialNum AND NOT EXISTS(SELECT 1 FROM #t WHERE serialNum=b.serialNum-1))-1 FROM #t a WHERE NOT EXISTS(SELECT 1 FROM #t WHERE serialNum=a.serialNum-1) --C SELECT STARTN=(SELECT max(serialNum) FROM #t b WHERE serialNum<a.serialNum AND NOT EXISTS(SELECT 1 FROM #t WHERE serialNum=b.serialNum+1))+1, ENDN=serialNum-1 FROM #T a WHERE serialNum>(SELECT MAX(serialNum)+1 FROM #t WHERE serialNum<a.serialNum)