求一个sql
表中有一个int列,我想求出这一列中大于1小于10的且不存在数字的集合
比如 表中用 5 ,6 那么求出来就是 1234789
我做的 但是不行
select packingno from CardInfo
where packingno not in
(
select packingno from CarInfo where packingno > 1 and packingno <10
)
and packingno between 1 and 10
表中有一个int列,我想求出这一列中大于1小于10的且不存在数字的集合
比如 表中用 5 ,6 那么求出来就是 1234789
我做的 但是不行
select packingno from CardInfo
where packingno not in
(
select packingno from CarInfo where packingno > 1 and packingno <10
)
and packingno between 1 and 10
select tt1.num+tt2.num+tt3.num id from (select 0 as num union all select 1 union all select 2 union all
select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all
select 9)tt1, (select 0 as num union all select 10 union all select 20 union all
select 30 union all select 40 union all select 50 union all
select 60 union all select 70 union all select 80 union all
select 90)tt2, (select 0 as num union all select 100 union all select 200 union all
select 300 union all select 400 union all select 500 union all
select 600 union all select 700 union all select 800 union all
select 900)tt3 where tt1.num+tt2.num+tt3.num between 1 and 600
)a where id not in( select 整型字段 from 表) and id>0 and id<10
order by id
能不能讲详细点 我用sql2000 还有效率如何数据量肯定小于10000条,我用的是c#没用存储过程
declare @val int,@digit int,@i int,@sql varchar(1000),@from varchar(4000),@ind varchar(2)
set @val=1000 --要生成的最大数
set @sql='' set @from=''
set @digit=len(@val)
set @i=0
while @i<@digit
begin
declare @zero varchar(9)
set @i=@i+1
set @zero=substring('000000000',1,@i-1)
set @ind=rtrim(convert(char(2),@i))
if @sql=''
set @sql=' tt'+@ind+'.num'
else
set @sql=@sql+'+tt'+@ind+'.num'
set @from=@from+' (select 0 as num union all select 1'+@zero+' union all select 2'+@zero+' union all
select 3'+@zero+' union all select 4'+@zero+' union all select 5'+@zero+' union all
select 6'+@zero+' union all select 7'+@zero+' union all select 8'+@zero+' union all
select 9'+@zero+')tt'+@ind+','
end
set @from=substring(@from,1,len(@from)-1)
set @sql='select '+@sql+' from '+@from+' where '+@sql+' between 1 and '+rtrim(convert(char(10),@val))+' order by '+@sql
print @sql
exec(@sql)
insert #T select 5
insert #T select 6select top 10 id=identity(int,1,1) into # from sysobjectsselect a.* from # a left join #T b on a.id=b.id where b.id is null
/*
id
-----------
1
2
3
4
7
8
9
10
*/
declare @data_number numeric(18,0)
declare @Max_number numeric(18,0),@Min_number numeric(18,0)
declare @return_table table (temp_data numeric(18,0))
set @Max_number=10
set @Min_number=1
set @data_number=@Min_number+1----取出两个值之间的数值插入temp_table
while @data_number<@Max_number
begin
insert into @return_table values (@data_number)
set @data_number=@data_number+1
end
----取出所要的数值
select temp_data from @return_table where temp_data not in(select packingno from CarInfo where packingno between 1 and 10 )