SQL获取连续数字中断数字

 

 

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