例如,表里有一列数据1,2,5,6,9,12,13,14,15,20,44,100。
需要查询没在这一列中的数如3~4,7~8,10~11,16~19,21~43,45~99。
1,使用子查询
略
2,用排序函数
WITH C1 AS
(
SELECT ROW_NUMBER() OVER(ORDER BY CardNo) AS rowNum, CardNumber FROM Table 1
)
,
C2 AS
(
SELECT CardNumber, (CardNumber - rowNum) AS grp FROM C1
UNION ALL
SELECT 0, 0
UNION ALL
SELECT @allowCount, @allowCount - (ISNULL(MAX(rowNum), 0) + 1) FROM C1
)
SELECT Max(CardNumber) + 1 AS cn FROM C2
GROUP BY grp
ORDER BY cn
查询结果:
3,8,25,45,89,97,10000
用SELECT * FROM C1
得到
rownum CardNum
1 1
2 2
3 5
4 6
5 7
6 24
7 44
8 88
9 96
10 9999
另外,用到的小技巧
1,在字符串左端补“0”, 用RIGHT函数。里RIGHT('000000' + CAST((Max(CardNumber) + 1)结果“98”变为“000098”
2,把“000098”转换为int 98:CAST(“000098” AS INT)。