declare @i int set @i=0 while @i<10 select a from table where a=....??? ^_^
create table tb(id int IDENTITY (1,1),col int); insert into tb(col) values(1) insert into tb(col) values(2) insert into tb(col) values(3) insert into tb(col) values(4); declare @mcol int select @mcol=3; /* SQL2005 WITH tbcte(id,col,temp) as ( select id,col,1 from tb where col=@mcol union all select id,col,temp+1 from tbcte where temp<10 ) select id,col from tbcte */ --sql2000 declare @mi int select @mi=0 create table #(id int,col int); while @mi<10 begin insert into #(id,col) select id,col from tb where col=@mcol select @mi=@mi+1 end select * from # drop table # drop table tb
先写一个select,然后用union all 连接9个一样的select语句 比如 select * from tablea union all select * from tablea union all select * from tablea union all select * from tablea ......
if object_id('tempdb..#t') is not null drop table #t declare @t table(id int,name varchar(10) ) insert @t select 1,'A' union all select 2,'B' union all select 3,'C'select top 10 id = identity(int,1,1) into #t from syscolumns select a.* from @t a,#t where a.id = 2drop table #t
set @i=0
while @i<10
select a from table where a=....??? ^_^
insert into tb(col) values(1)
insert into tb(col) values(2)
insert into tb(col) values(3)
insert into tb(col) values(4);
declare @mcol int
select @mcol=3;
/* SQL2005
WITH tbcte(id,col,temp)
as
(
select id,col,1 from tb where col=@mcol
union all
select id,col,temp+1 from tbcte where temp<10
)
select id,col from tbcte
*/
--sql2000
declare @mi int
select @mi=0
create table #(id int,col int);
while @mi<10
begin
insert into #(id,col) select id,col from tb where col=@mcol
select @mi=@mi+1
end
select * from #
drop table #
drop table tb
比如
select * from tablea
union all
select * from tablea
union all
select * from tablea
union all
select * from tablea
......
drop table #t
declare @t table(id int,name varchar(10) )
insert @t
select 1,'A' union all
select 2,'B' union all
select 3,'C'select top 10 id = identity(int,1,1) into #t from syscolumns
select a.* from @t a,#t where a.id = 2drop table #t