有表Tb的唯一字段Id(范围0-255)
1、查询一个未用Id
2、查询一个未用且最小的Id
3、查询一个未用且最大的Id
网上很多的SQL用下表会失败
Tb:
======================
Id xxx
254 ccc
255 ...
======================
有邹建提供:
SQL code
-- 你的源表 DECLARE @tb TABLE( Id int ) INSERT @tb SELECT 254 UNION ALL SELECT 1 UNION ALL SELECT 255 -- 1、查询一个未用Id SELECT * FROM( SELECT id = A.id + (B.id * 4) + (C.id * 16) + (D.id * 64) FROM( SELECT id = 0 UNION ALL SELECT id = 1 UNION ALL SELECT id = 2 UNION ALL SELECT id = 3 )A, ( SELECT id = 0 UNION ALL SELECT id = 1 UNION ALL SELECT id = 2 UNION ALL SELECT id = 3 )B, ( SELECT id = 0 UNION ALL SELECT id = 1 UNION ALL SELECT id = 2 UNION ALL SELECT id = 3 )C, ( SELECT id = 0 UNION ALL SELECT id = 1 UNION ALL SELECT id = 2 UNION ALL SELECT id = 3 )D )ID WHERE NOT EXISTS( SELECT * FROM @tb WHERE Id = ID.id) -- 2、查询一个未用且最小的Id SELECT MIN(Id) + 1 FROM( SELECT Id FROM @tb UNION ALL SELECT Id = -1 ) A WHERE NOT EXISTS( SELECT * FROM @tb WHERE Id = A.Id + 1) -- 3、查询一个未用且最大的Id SELECT MAX(Id) - 1 FROM( SELECT Id FROM @tb UNION ALL SELECT Id = 256 ) A WHERE NOT EXISTS( SELECT * FROM @tb WHERE Id = A.Id - 1)
如果第一个问题是任意一个未用的
那么可以直接用问题2或者3的答案