表A
-- 创建结果表
create table #u(LostA int)
declare @minA int,@maxA int
set @minA=(select min(ID) from A)
set @maxA=(select max(ID) from A)
while(@minA<=@maxA)
begin
if not exists(select 1 from Awhere ID=@minA)
begin
insert into #u(LostA) values(@minA)
end
select @minA=@minA+1
end
select * from #u
或者
declare @minA int,@maxA int;
set @minA=(select min(ID) from A);
set @maxA=(select max(ID) from A);
WITH A AS
(
SELECT @minA AS num
UNION ALL
SELECT num+1 FROM A
WHERE num<@maxA
)
SELECT num FROM A where A.num not in (select ID from A)
OPTION(MAXRECURSION 0) --当指定MAXRECURSION为0时,递归层次无限制,100为系统的默认值