declare @s varchar(8000) select top 5 @s=isnull(@s+',','')+id7 from abc order by newid() select @s这样吗
DECLARE @str VARCHAR(8000) SET @str = '';SELECT TOP 5 @str = @str + ',' + id7 FROM abc ORDER BY NEWID();SELECT @str;
declare @s varchar(8000) select top 5 @s=isnull(@s+',','')+ltrim(id7) from abc order by newid() select @smodify
不行啊。。这招我用过了。。@s最终只能输出一个id7
if object_id('[tb]') is not null drop table [tb] go create table [tb]([name] varchar(10),[id7] int) insert [tb] select 'Tom',1 union all select 'Tom',2 union all select 'Jerry',1 union all select 'Jerry',2 union all select 'Jerry',10 union all select 'Jack',23 union all select 'Jack',15declare @s varchar(8000) select top 5 @s=isnull(@s+',','')+ltrim(id7) from tb order by newid() select @s /*---------- 10,2,1,15,23(1 行受影响)*/
create proc pr_test @col varchar(100), @ret varchar(1000) output as begin declare @s nvarchar(1000) set @s = N'select isnull(@ret+'','','''')'+@col+' from abc order by newid() ' exec sp_excutesql @s,'@ret varchar(1000) out',@ret out end go
create proc pr_test @col varchar(100), @ret varchar(1000) output as begin declare @s nvarchar(1000) set @s = N'select isnull(@ret+'','','''')'+@col+' from abc order by newid() ' exec sp_excutesql @s,'@ret varchar(1000) out',@ret out end go
create proc pr_test @col varchar(100), @ret varchar(1000) output as begin declare @s nvarchar(1000) set @s = N'select isnull(@ret+'','','''')'+@col+' from abc order by newid() ' exec sp_executesql @s,'@ret varchar(1000) out',@ret out end go
。。不加随机还好。。一加就只能输出一个id7 一定是newid()的问题........
if object_id('[tb]') is not null drop table [tb] go create table [tb]([name] varchar(10),[id7] int) insert [tb] select 'Tom',1 union all select 'Tom',2 union all select 'Jerry',1 union all select 'Jerry',2 union all select 'Jerry',10 union all select 'Jack',23 union all select 'Jack',15go create proc pr_test @col varchar(100), @ret varchar(1000) output as begin declare @s nvarchar(1000) set @s = N'select top 5 @ret = isnull(@ret+'','','''')+ltrim('+@col+') from [tb] order by newid() ' exec sp_executesql @s,N'@ret varchar(1000) out',@ret out end go declare @s varchar(100) exec pr_test 'id7',@s out select @s drop proc pr_test/* ---------------------------------------------------------------------------------------------------- 2,23,1,2,15(1 行受影响)*/
declare @s varchar(8000)
select top 5 @s=isnull(@s+',','')+id7 from abc order by newid()
select @s这样吗
SET @str = '';SELECT TOP 5
@str = @str + ',' + id7
FROM abc
ORDER BY NEWID();SELECT @str;
select top 5 @s=isnull(@s+',','')+ltrim(id7) from abc order by newid()
select @smodify
go
create table [tb]([name] varchar(10),[id7] int)
insert [tb] select 'Tom',1
union all select 'Tom',2
union all select 'Jerry',1
union all select 'Jerry',2
union all select 'Jerry',10
union all select 'Jack',23
union all select 'Jack',15declare @s varchar(8000)
select top 5 @s=isnull(@s+',','')+ltrim(id7) from tb order by newid()
select @s
/*----------
10,2,1,15,23(1 行受影响)*/
@col varchar(100),
@ret varchar(1000) output
as
begin
declare @s nvarchar(1000)
set @s = N'select isnull(@ret+'','','''')'+@col+' from abc order by newid() '
exec sp_excutesql @s,'@ret varchar(1000) out',@ret out
end
go
@col varchar(100),
@ret varchar(1000) output
as
begin
declare @s nvarchar(1000)
set @s = N'select isnull(@ret+'','','''')'+@col+' from abc order by newid() '
exec sp_excutesql @s,'@ret varchar(1000) out',@ret out
end
go
@col varchar(100),
@ret varchar(1000) output
as
begin
declare @s nvarchar(1000)
set @s = N'select isnull(@ret+'','','''')'+@col+' from abc order by newid() '
exec sp_executesql @s,'@ret varchar(1000) out',@ret out
end
go
一定是newid()的问题........
go
create table [tb]([name] varchar(10),[id7] int)
insert [tb] select 'Tom',1
union all select 'Tom',2
union all select 'Jerry',1
union all select 'Jerry',2
union all select 'Jerry',10
union all select 'Jack',23
union all select 'Jack',15go
create proc pr_test
@col varchar(100),
@ret varchar(1000) output
as
begin
declare @s nvarchar(1000)
set @s = N'select top 5 @ret = isnull(@ret+'','','''')+ltrim('+@col+') from [tb] order by newid() '
exec sp_executesql @s,N'@ret varchar(1000) out',@ret out
end
go
declare @s varchar(100)
exec pr_test 'id7',@s out
select @s
drop proc pr_test/*
----------------------------------------------------------------------------------------------------
2,23,1,2,15(1 行受影响)*/