求1个sql查询语句解决方案

create table 表A(Aid int, Aname varchar(10), Abeizhu varchar(5))insert into 表Aselect 1, 'Aname1', 'Abz1' union allselect 2, 'aname2', 'Abz2' union allselect 3, 'Aname3', 'Abz3'create table 表B(Bid int, Bname varchar(10), Bbeizhu varchar(5), Aid int)  insert into 表Bselect 1, 'Bname1', 'Bbz1', 1 union allselect 2, 'Bname2', 'Bbz2', 1 union allselect 3, 'Bname3', 'Bbz3', 2 union allselect 4, 'Bname1', 'Bbz4', 2 union allselect 5, 'Bname2', 'Bbz5', 3 union allselect 6, 'Bname3', 'Bbz6', 3select a.Aid,a.Aname,a.Abeizhu,b.beizhufrom 表A ainner join (select Aid,cast((select Bbeizhu+';' from 表B b1 where b1.Aid=b0.Aid for xml path('')) as varchar) beizhufrom 表B b0group by Aid) bon a.Aid=b.AidAid         Aname      Abeizhu beizhu----------- ---------- ------- ------------------------------1           Aname1     Abz1    Bbz1;Bbz2;2           aname2     Abz2    Bbz3;Bbz4;3           Aname3     Abz3    Bbz5;Bbz6;(3 row(s) affected)