查询表中已删除id的方法

来源：互联网发布：腾讯mac 编辑：程序博客网时间：2024/05/09 13:21

方法一: 创建一个满足需求的很大的View

create view v_number --创建一个view，里面存放1到9999的数据，如果需要更大可以根据自己的情况修改
as
select number from
(
select t1.b+t2.b*10+t3.b*100+t4.b*1000 number
from
(
select 0 b union all select 1 b union all select 2 b union all
select 3 b union all select 4 b union all select 5 b union all
select 6 b union all select 7 b union all select 8 b union all select 9 b
) t1,
(
select 0 b union all select 1 b union all select 2 b union all
select 3 b union all select 4 b union all select 5 b union all
select 6 b union all select 7 b union all select 8 b union all select 9 b
) t2,
(
select 0 b union all select 1 b union all select 2 b union all
select 3 b union all select 4 b union all select 5 b union all
select 6 b union all select 7 b union all select 8 b union all select 9 b
) t3,
(
select 0 b union all select 1 b union all select 2 b union all
select 3 b union all select 4 b union all select 5 b union all
select 6 b union all select 7 b union all select 8 b union all select 9 b
) t4
) t5
where number<>0

--用左外连接找到clothid中的cid不在v_number中的数
select number
from v_number left outer join YWXX on number = YWID
where YWID is null and number <(select max(YWID ) from YWXX )
order by number

方法二:

创建一个临时表
declare   @temptable   table(a   int)

declare   @char     varchar(1000),@i   int,@num   int
set   @num=(select   max(YWID)   from   YWXX)
set   @i=1
while   @i<=@num
begin
      insert   @temptable   values   (@i)
      set   @i=@i+1
end
select   a   from   @temptable   where   a   not   in   (select YWID from YWXX)