日期:2014-05-18  浏览次数:20559 次

求1个sql查询语句
表A 
 Aid Aname Abeizhu
  1 Aname1 Abz1
  2 aname2 Abz2
  3 Aname3 Abz3
表B
  Bid Bname Bbeizhu Aid
  1 Bname1 Bbz1 1
  2 Bname2 Bbz2 1
  3 Bname3 Bbz3 2
  4 Bname1 Bbz4 2
  5 Bname2 Bbz5 3
  6 Bname3 Bbz6 3

如何查询时把B表的备注变成字符串

如下
  Aid Aname Abeizhu beizhu
  1 Aname1 Abz1 Bbz1;Bbz2;
  2 Aname2 Abz2 Bbz3;Bbz4;
  3 Aname3 Abz3 Bbz5;Bbz6;



------解决方案--------------------
SQL code

create table 表A
(Aid int, Aname varchar(10), Abeizhu varchar(5))

insert into 表A
select 1, 'Aname1', 'Abz1' union all
select 2, 'aname2', 'Abz2' union all
select 3, 'Aname3', 'Abz3'

create table 表B
(Bid int, Bname varchar(10), Bbeizhu varchar(5), Aid int)
  
insert into 表B
select 1, 'Bname1', 'Bbz1', 1 union all
select 2, 'Bname2', 'Bbz2', 1 union all
select 3, 'Bname3', 'Bbz3', 2 union all
select 4, 'Bname1', 'Bbz4', 2 union all
select 5, 'Bname2', 'Bbz5', 3 union all
select 6, 'Bname3', 'Bbz6', 3


select a.Aid,a.Aname,a.Abeizhu,
b.beizhu
from 表A a
inner join 
(select Aid,
cast((select Bbeizhu+';' from 表B b1 where b1.Aid=b0.Aid for xml path('')) as varchar) beizhu
from 表B b0
group by Aid) b
on a.Aid=b.Aid

Aid         Aname      Abeizhu beizhu
----------- ---------- ------- ------------------------------
1           Aname1     Abz1    Bbz1;Bbz2;
2           aname2     Abz2    Bbz3;Bbz4;
3           Aname3     Abz3    Bbz5;Bbz6;

(3 row(s) affected)