关于ABC三张表关联的问题 现在出现了有些信息重复

[张dozen]

A B C 三张表 A是主表 bc是两张从属表 现在做wmconcat group by A的主键的时候 会有重复
假如 b的从属信息只有一条，c的从属信息有2条 b的信息就会被重复多出一条。下面是测试的sql语句
create table testA (
ID varchar2(20)
);

create table testB (
AID varchar2(20),
bname varchar2(20)
);

create table testC (
AID varchar2(20),
cname varchar2(20)
);

insert into testA(ID) values('id1');

insert into testA(ID) values('id2');


insert into testB(AID,bname) values('id1','bname1');


insert into testC(AID,cname) values('id1','cname1');

insert into testC(AID,cname) values('id1','cname2');



SELECT a.ID,
WM_CONCAT(b.bname) bnames,
WM_CONCAT(c.cname) cnames
FROM testA a
LEFT OUTER JOIN testB b
ON b.aid = a.id

LEFT OUTER JOIN testC c
ON c.aid = a.id
GROUP BY a.id
查找的结果如下
1 id1 bname1,bname1 cname1,cname2
2 id2

bname1重复了两下
如何能让 bname1 只显示一条

[张dozen]

貌似 分两次select 就木有问题了 select d.id,max(d.bnames) ,WM_CONCAT(c.cname) cnames from ( SELECT a.ID, WM_CONCAT(b.bname) bnames FROM testA a LEFT OUTER JOIN testB b ON b.aid = a.id GROUP BY a.id ) d LEFT OUTER JOIN testC c ON c.aid = d.id GROUP BY d.id

[张dozen]

但是我上面的语句就显得很麻烦 有没有更方便 性能代价更小的方案

[大明湖畔_帅锅]

这样可以么..

select a.id,b.bname,c.cname 
from testa a 
     left join 
     (select aid,wm_concat(bname) bname from testb group by aid) b on a.id = b.aid
     left join 
     (select aid,wm_concat(cname) cname from testc group by aid) c on  a.id = c.aid

[张dozen]

引用 3 楼 HJ_daxian 的回复:

这样可以么..

select a.id,b.bname,c.cname 
from testa a 
     left join 
     (select aid,wm_concat(bname) bname from testb group by aid) b on a.id = b.aid
     left join 
     (select aid,wm_concat(cname) cname from testc group by aid) c on  a.id = c.aid

这个方式我试过了 ，很伤性能 要10s才出来结果