编写SQL：看似简单，实际超难的SQL语句！

有表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 @tbSELECT 254 UNION ALLSELECT 1 UNION ALLSELECT 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)IDWHERE NOT EXISTS(        SELECT * FROM @tb        WHERE Id = ID.id)-- 2、查询一个未用且最小的Id SELECT    MIN(Id) + 1FROM(    SELECT Id FROM @tb    UNION ALL    SELECT Id = -1 ) AWHERE NOT EXISTS(        SELECT * FROM @tb        WHERE Id = A.Id + 1)-- 3、查询一个未用且最大的Id SELECT    MAX(Id) - 1FROM(    SELECT Id FROM @tb    UNION ALL    SELECT Id = 256 ) AWHERE NOT EXISTS(        SELECT * FROM @tb        WHERE Id = A.Id - 1)

 
如果第一个问题是任意一个未用的 

那么可以直接用问题2或者3的答案