declare @s varchar(8000)
set @s =''
Select @s=@s+ address From address Group By stuid
Select stuid, stuname, @s=@s+ (Select address From address Where stuid =a.stuid Group By stuid ) From stu group by stuid, stuname
set @s =''
Select @s=@s+ address From address Group By stuid
Select stuid, stuname, @s=@s+ (Select address From address Where stuid =a.stuid Group By stuid ) From stu group by stuid, stuname
set @s =''
--Select @s=@s+ address From address Group By stuid
Select stuid, stuname, @s=@s+ (Select address From address Where stuid =a.stuid Group By stuid ) From stu group by stuid, stuname
向变量赋值的 SELECT 语句不能与数据检索操作结合使用。
create table stu
(
stuid varchar(10),stuname varchar(10)
)
create table address
(
stuid varchar(10),address varchar(50)
)
insert stu
select '1000','sun' union select '1001','hu'
insert address
select '1000','浙江' union select '1000','天津' union
select '1001','北京' union select '1001','天津'
go
create function getstr
(
@stuid varchar(10)
)
returns varchar(1000)
as
begin
--declare @stuid varchar(10)
--set @stuid='1000'
declare @str varchar(1000)
set @str=''
select @str=@str+' '+address from address where stuid=@stuid
--select ltrim(@str)
return ltrim(@str)
end
go--测试
select distinct A.stuid,A.stuname,[address]=dbo.getstr(A.stuid)
from stu A join address B on A.stuid=B.stuid --删除测试环境
drop table stu
drop table address
drop function getstr--结果
/*
stuid stuname address
---------- ---------- ------------
1000 sun 天津 浙江
1001 hu 北京 天津(所影响的行数为 2 行)
*/
returns varchar(8000)
as
declare @s varchar(8000)
set @s =''
Select @s=@s+' '+ address From address where stuid=@stuid
return(@s)
goSelect stuid, stuname, dbo.yfun(stuid)address
From stu, address
where stu.stuid=address.stuid
Select a.stuid, a.stuname, b.dbo.yfun(stuid)address
From stu a, address b
where a.stuid=b.stuid