一个查询相关的问题
表A
id page
1 a
2 b
3 c
表B
page name
a aa
a ab
a ac
b bd
b bg
c ac
c fd
需生成表C
id page name
1 a aa,ab,ac
2 b bd,bg
3 c fd,ac
[解决办法]
drop table 表A,表B
create table 表A(id int,page varchar(10))
insert into 表A
select 1, 'a '
union all select 2, 'b '
union all select 3, 'c '
create table 表B(page varchar(10),name varchar(10))
insert into 表B
select 'a ', 'aa '
union all select 'a ', 'ab '
union all select 'a ', 'ac '
union all select 'b ', 'bd '
union all select 'b ', 'bg '
union all select 'c ', 'ac '
union all select 'c ', 'fd '
create function f_getstr(@id varchar(10))
returns varchar(8000)
as
begin
declare @returnstr varchar(8000)
select @returnstr = ' '
select @returnstr = @returnstr + name+ ', ' from 表B where page=@id
return left(@returnstr,len(@returnstr)-1)
end
select id,dbo.f_getstr(page) as name
from 表A
