大体 思路和:declare @t table(col int) insert @t select '2' insert @t select '3' insert @t select '8'select a.* from @t a full join (select 1 as id union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8) b on a.col >= b.id/*col ----------- 2 3 8 2 3 8 3 8 8 8 8 8 8(所影响的行数为 13 行) */
create table #(Id int,name varchar(10))insert into # select '2','aa' insert into # select '3','bb'select Id=identity(int,1,1) into #t from sysobjects a,sysobjects bselect a.* from # a,#t b where a.id>=b.id
'考虑数字是比较大情况下' create function xx (@s int ) returns @t table (a int ) as Begin declare @a int set @a=1 while @a<=@s Begin insert into @t select @s set @a=@a+1 End return End select * from dbo.xx(18)
insert @t select '2'
insert @t select '3'
insert @t select '8'select a.*
from @t a
full join (select 1 as id union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8) b
on a.col >= b.id/*col
-----------
2
3
8
2
3
8
3
8
8
8
8
8
8(所影响的行数为 13 行)
*/
insert into # select '3','bb'select Id=identity(int,1,1) into #t from sysobjects a,sysobjects bselect a.* from # a,#t b
where a.id>=b.id
create function xx (@s int )
returns @t table (a int )
as
Begin
declare @a int
set @a=1
while @a<=@s
Begin
insert into @t select @s
set @a=@a+1
End
return
End
select * from dbo.xx(18)