--参考 表结构如下: 列名 数据类型 长度 允许为空 id int 4 是 test varchar 50 是 Rank int 4 是 数据内容如下: id test Rank 1 Hello 1 1 Mr 2 1 King 3 2 I 1 2 am 2 2 Best 4 2 The 3查询结果如下: id test 1 Hello Mr King 2 I am The Best用SQL实现结果--我的实现 create table t(id int,test varchar(50),Rank int) insert into t select 1,'Hello',1 insert into t select 1,'Mr',2 insert into t select 1,'King',3 insert into t select 2,'I',1 insert into t select 2,'am',2 insert into t select 2,'Best',4 insert into t select 2,'The',3--定义游标 declare @t table(a int) declare @sql varchar(100) set @sql='' declare @lflag int declare csr1 cursor for select distinct id from t open csr1 fetch next from csr1 into @lflag while @@fetch_status=0 begin fetch next from csr1 into @lflag select @sql=@sql+' '+test from t where id=@lflag order by rank select stuff(@sql,1,1,'') end close csr1deallocate csr1drop table t
表结构如下:
列名 数据类型 长度 允许为空
id int 4 是
test varchar 50 是
Rank int 4 是
数据内容如下:
id test Rank
1 Hello 1
1 Mr 2
1 King 3
2 I 1
2 am 2
2 Best 4
2 The 3查询结果如下: id test
1 Hello Mr King
2 I am The Best用SQL实现结果--我的实现
create table t(id int,test varchar(50),Rank int)
insert into t select 1,'Hello',1
insert into t select 1,'Mr',2
insert into t select 1,'King',3
insert into t select 2,'I',1
insert into t select 2,'am',2
insert into t select 2,'Best',4
insert into t select 2,'The',3--定义游标
declare @t table(a int)
declare @sql varchar(100)
set @sql=''
declare @lflag int
declare csr1 cursor for
select distinct id from t
open csr1
fetch next from csr1 into @lflag
while @@fetch_status=0
begin
fetch next from csr1 into @lflag
select @sql=@sql+' '+test from t where id=@lflag order by rank
select stuff(@sql,1,1,'')
end
close csr1deallocate csr1drop table t