declare @table table(id int)
insert @table values(2)
insert @table values(4)
insert @table values(7)select b.iid from
@table a right join
(
select 1 iid
union all
select 2 iid
union all
select 3 iid
union all
select 4 iid
union all
select 5 iid
union all
select 6 iid
union all
select 7 iid
union all
select 8 iid
) b
on a.id = b.iid
where id is null/*
iid
-----------
1
3
5
6
8(所影响的行数为 5 行)
*/
insert @table values(2)
insert @table values(4)
insert @table values(7)select b.iid from
@table a right join
(
select 1 iid
union all
select 2 iid
union all
select 3 iid
union all
select 4 iid
union all
select 5 iid
union all
select 6 iid
union all
select 7 iid
union all
select 8 iid
) b
on a.id = b.iid
where id is null/*
iid
-----------
1
3
5
6
8(所影响的行数为 5 行)
*/
select @i=max(id)-1 from yourtablecreate table #t (id int)
while @i>0
begin
insert into #t values (@i)
@i=@i-1
endselect a.id
from #t a left join yourtable b on a.id=b.id
where b.id is nulldrop table #t