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 id2bname1重复了两下
如何能让 bname1 只显示一条
假如 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 id2bname1重复了两下
如何能让 bname1 只显示一条
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
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