怎么查询相同的记录

[weisai]

表中符合条件的只有一条记录。
但我要这条记录重复出现10行，select语句怎么写
以前写过的，现在忘了。

谢谢

[xyxfly]

declare @i int
set @i=0
while @i<10
select a from table where a=....

??? ^_^

[weisai]

没有那么复杂吧

[jieyh]

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

[fjye]

先写一个select，然后用union all 连接9个一样的select语句
比如
select * from tablea
union all
select * from tablea
union all
select * from tablea
union all
select * from tablea
......

[hellowork]

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 = 2

drop table #t