sql 查询问题.帮忙啊..
tb1
id vid
1 10
2 20
3 30
tb2
vid nama
10 a
20 b
20 c
30 d
30 e
30 f
查询的结果是
id vid name
1 10 a
2 20 d,c
3 30 d,e,f
请问这怎么查询啊.
[解决办法]
drop table tb1,tb2
create table tb1(id int,vid int)
insert into tb1
select 1,10
union all select 2,20
union all select 3,30
create table tb2(vid int,nama varchar(20))
insert into tb2
select 10, 'a '
union all select 20, 'b '
union all select 20, 'c '
union all select 30, 'd '
union all select 30, 'e '
union all select 30, 'f '
alter function f_getstr(@vid int)
returns varchar(8000)
as
begin
declare @returnstr varchar(8000)
select @returnstr = ' '
select @returnstr = @returnstr + nama+ ', ' from tb2 where vid=@vid
return left(@returnstr,len(@returnstr)-1)
end
select id,vid,dbo.f_getstr(vid) as name
from tb1
