34,590
社区成员
发帖
与我相关
我的任务
分享
with s as
(
select MAX(id) id from table1
)
,t as
(
select 1 as dt
union all
select dt+1 from t, s
where dt+1<=s.id
)
select dt from t where dt not in (select id from table1) option(maxrecursion 0)
-- 参考一下这个
create table test(id int identity,name varchar(10))
go
insert into test(name) values('')
go
-- 写入65536 行数据
insert into test(name) select name from test
go 16
-- 随机删除几条
delete test where id in (100,200,400,1000,2000,4000,7000)
go
with m as (
select row_number() over(order by (select 1)) rn
from sys.columns a, sys.columns b, sys.columns c
)
select * from m left join test on m.rn = test.id
where m.rn <= 65536 and test.id is null
order by rn
go
drop table test
go
(32768 行受影响)
批处理执行已完成 16 次。
(7 行受影响)
rn id name
-------------------- ----------- ----------
100 NULL NULL
200 NULL NULL
400 NULL NULL
1000 NULL NULL
2000 NULL NULL
4000 NULL NULL
7000 NULL NULL
(7 行受影响)