SQL获取连续数字中断数字

 

 

表A

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

-- 创建结果表 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

 或者

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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为系统的默认值