34,837
社区成员




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
update ttb set id = @Row
set @Row=@Row+1
end
end
--环境
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'
go
create table ttb(id int identity(1,1),[name] char(100))
select * from ttb
delete from ttb
go
--存储过程
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
end
end
--删除环境
drop table ttb
go
drop table tb
go
drop proc GetTop
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 .......
declare @sql varchar(1000)
select @sql=isnull(@sql+',','')+aa from (select top 5 * from ab order by newid())a
update ab2 set aa=@sql where ......