编号问题

　　编号问题的使用场景很多，比方考勤、工资发放这些都可能用到编号。简单写下常会用到的编号问题，备忘：

 

　　

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)

--已用编号分布
SELECT 
STARTN=serialNum,
ENDN=(SELECT MIN(serialNum) FROM #t b WHERE  serialNum>=a.serialNum AND  NOT EXISTS(SELECT 1 FROM #t WHERE  serialNum=b.serialNum+1))
FROM #t a
WHERE NOT EXISTS(SELECT 1 FROM #t WHERE serialNum=a.serialNum-1)

　　

 

　　当然上面的写法并不是很完善，没有考虑到初始编号和终止编号的问题。如果需要可以采取补号的方式来规避。

 

 

转载于:https://www.cnblogs.com/mfkaudx/p/3394747.html