[SQL] 分析函數應用 - 找出最小值

公司內某流水號商業邏輯，需要找到最小流水號加一當成新的流水號使用，但因為混雜太多規則，最後是採用 Lead() 來達到需求

Sample Code 如下，邏輯是透過 Lead() 找到該筆資料下一筆後，兩者相減大於一後，全部資料最小值，即為目前流水號最小值，以 Sample Data 來看是 0003

DECLARE @Temp TABLE ([NO] char(5))
INSERT INTO @Temp ([NO]) VALUES('0001')
INSERT INTO @Temp ([NO]) VALUES('0002')
INSERT INTO @Temp ([NO]) VALUES('0003')
INSERT INTO @Temp ([NO]) VALUES('1001')
INSERT INTO @Temp ([NO]) VALUES('1002')
INSERT INTO @Temp ([NO]) VALUES('8100')
INSERT INTO @Temp ([NO]) VALUES('8101')
INSERT INTO @Temp ([NO]) VALUES('8102')
INSERT INTO @Temp ([NO]) VALUES('A0012')
INSERT INTO @Temp ([NO]) VALUES('B0034')
INSERT INTO @Temp ([NO]) VALUES('M123')


SELECT 
	MIN([current]) AS GapMinNO
FROM
	(
		SELECT 
			[NO] AS [current],
			LEAD([NO]) OVER (ORDER BY [NO]) AS [next]
		FROM @Temp
		WHERE Try_Cast([NO] as int) IS NOT NULL
	) AS T
WHERE CAST([next] as int) - CAST([current] as int) > 1

• 參考資料
• LEAD
• SQL Server Window Functions Gaps and Islands Problem