再加了个判断语句不就可以了吧…… where class in (select distinct class from 表A)试试看
回复人: zjcxc(邹建) ( ) 信誉:298 --测试--测试数据 create table 表(id int,[temp] varchar(10)) insert 表 select 1,'a' union all select 1,'b' union all select 2,'c' go--创建处理函数 create function f_merg(@id int) returns varchar(8000) as begin declare @r varchar(8000) set @r='' select @r=@r+temp from 表 where id=@id return(@r) end go--调用实现要求 select id,dbo.f_merg(id) from 表 group by id go--删除测试 drop table 表/*--测试结果 id ----------- ------------- 1 ab 2 c(所影响的行数为 2 行)--*/
楼上的不能达到要求,函数修改如下 create function f_merg(@class int) returns varchar(8000) as begin declare @r varchar(8000) set @r='' select @r=@r+','+name from table1 where class=@class return(@r) end 查询语句 select class,substring(dbo.f_merg(class),2,len(dbo.f_merg(class))-1) from table1 group by class
id, class, name, age .....
1, 1, a1, 20 .....
2, 1, b1, 15 .....
3, 1, c1, 24 .....
4, 2, a2, 20 .....
5, 2, b2, 15 .....
6, 2, c2, 24 .....
7, 3, a3, 20 .....
8, 3, b3, 15 .....
9, 3, c3, 24 .....
....
查询结果
class students
1 a1,b1,c1....
2 a2,b2,c2....
3 a3,b3,c3....
......
--测试--测试数据
create table 表(id int,[temp] varchar(10))
insert 表 select 1,'a'
union all select 1,'b'
union all select 2,'c'
go--创建处理函数
create function f_merg(@id int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+temp from 表 where id=@id
return(@r)
end
go--调用实现要求
select id,dbo.f_merg(id) from 表 group by id
go--删除测试
drop table 表/*--测试结果
id
----------- -------------
1 ab
2 c(所影响的行数为 2 行)--*/
create function f_merg(@class int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+name from table1 where class=@class
return(@r)
end
查询语句
select class,substring(dbo.f_merg(class),2,len(dbo.f_merg(class))-1)
from table1 group by class