方法一: 创建一个满足需求的很大的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)