有表Tb的唯一字段Id(范围0-255)
1、查询一个未用Id
2、查询一个未用且最小的Id
3、查询一个未用且最大的Id
网上很多的SQL用下表会失败
Tb:
======================
Id xxx
254 ccc
255 ...
======================
1、查询一个未用Id
2、查询一个未用且最小的Id
3、查询一个未用且最大的Id
网上很多的SQL用下表会失败
Tb:
======================
Id xxx
254 ccc
255 ...
======================
declare @tb table(Id int)
insert into @tb
select 1 union
select 2 union
select 4 union
select 5 union
select 6 union
select 9 union
select 10select top 1 b.id
from (select top 10 (select count(*) from sysobjects where id<=a.id) as id from sysobjects a order by id) b
left join @tb c on b.id=c.id
where c.id is null
order by newid()select min(b.id) as id
from (select top 10 (select count(*) from sysobjects where id<=a.id) as id from sysobjects a order by id) b
left join @tb c on b.id=c.id
where c.id is nullselect max(b.id) as id
from (select top 10 (select count(*) from sysobjects where id<=a.id) as id from sysobjects a order by id) b
left join @tb c on b.id=c.id
where c.id is null
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)
select min(a.id) from (select [id]+1 [id] from tb)a left join tb on tb.id=a.id where tb.id is null and a.id<256