表 AB 字段 id aa
1 aaaa
2 bbbb
3 cccc
其中AB表中有很多记录，我想随机取出5条，并把字段aa组合成一个以‘，’分隔的长字符串，更新到另一表中，不知道怎样写好
create proc xxxxxxx
as
begin
declare @bbb varchar(200)
--随机选出记录并组合成字符串传给变量@bbb 例如：aaaa,bbbb,cccc,(这部分怎么写？)
update ab2 set aa = @bbb where ....
end
------解决方案--------------------
create proc xxxxxxx
as
begin
declare @bbb varchar(200)
select @bbb=isnull(@bbb+',','')+ aa from (select top 5 bb from ab order by newid()) bb
update ab2 set aa = @bbb where ....
end
------解决方案--------------------
declare @linkSTR varchar(200)
select top 5 @linkSTR=isnull(@linkSTR,'') + ',' + aa from ab order by newid()
update ab2 set aa=stuff(@linkSTR,1,1,'') where ...
------解决方案--------------------
- SQL code
declare @sql varchar(1000)select @sql=isnull(@sql+',','')+aa from (select top 5 * from ab order by newid())aupdate ab2 set aa=@sql where ......
------解决方案--------------------
declare @str varchar(200)
select @str = isnull(@str+',','')+aa from (select top 5 * from AB) bb
print @str
这样就可以查出你要的数据了
------解决方案--------------------
- SQL code
declare @s nvarchar(1000) select @s=isnull(@s+',','')+quotename(Name) from (select top 5 Name from 表名 order by newiD())t update 表名 set Name=@s where .......
------解决方案--------------------
- SQL code
--环境create table tb(id int identity(1,1),[name] char(10))insert into tb select 'a'insert into tb select 'b'insert into tb select 'c'insert into tb select 'd'insert into tb select 'e'insert into tb select 'f'gocreate table ttb(id int identity(1,1),[name] char(100))select * from ttbdelete from ttbgo--存储过程create proc GetTop as begin declare @Row int set @Row=0 while @Row < 5 begin declare @sql nvarchar(200) set @sql='' select @sql=@sql+[name] from (select top 5 * from tb order by newid()) t insert ttb select name = @sql set @Row=@Row+1 endend--删除环境drop table ttbgodrop table tbgodrop proc GetTop
------解决方案--------------------
create table tb(id varchar(10),username varchar(10))
insert into tb values('1', '123')
insert into tb values('2', '234')
insert into tb values('3', 'hell')
go
declare @a varchar(5000),@b varchar(5000)
select @a='',@b=''
select @a=@a+'，'+rtrim(id),@b=@b+'，'+username from tb
select 'id',stuff(@a,1,1,'') union select 'username',stuff(@b,1,1,'')
drop table tb
/*
-------- --------------
id 1，2，3
username 123，234，hell
（所影响的行数为 2 行）
*/