3,490
社区成员
发帖
与我相关
我的任务
分享
SELECT *
FROM (SELECT wlbh, nvl(cssl, 0) cssl, nvl(rksl, 0) rksl, nvl(cksl, 0) cksl
FROM (SELECT wlbh, SUM(cssl) cssl FROM tableA GROUP BY wlbh)
FULL OUTER JOIN (SELECT wlbh, SUM(rksl) rksl
FROM tableB
GROUP BY wlbh)
USING (wlbh)
FULL OUTER JOIN (SELECT wlbh, SUM(cksl) cksl
FROM tableC
GROUP BY wlbh)
USING (wlbh))
ORDER BY wlbh;
SQL> with ta as(
2 select 101 wlbh,4 cssl from dual union all
3 select 301,5 from dual union all
4 select 501,6 from dual union all
5 select 102,0 from dual union all--这两项是我加上的,总觉得不是你的意思
6 select 201,0 from dual)--说实话,你的表设计的太不合理了,这么大的数据冗余你尽然没发现?
7 ,tb as(
8 select 101 wlbh,2 rksl from dual union all
9 select 102,4 from dual union all
10 select 301,5 from dual union all
11 select 101,1 from dual)
12 ,tc as(
13 select 101 wlbh,3 cksl from dual union all
14 select 102,1 from dual union all
15 select 201,5 from dual union all
16 select 101,2 from dual)
17 select ta.wlbh,
18 nvl(sum(ta.cssl),0) cssl,
19 nvl(sum(b.rksl),0) rksl,
20 nvl(sum(c.cksl),0) cksl
21 from ta,
22 (select wlbh,sum(rksl) rksl
23 from tb
24 group by wlbh) b,
25 (select wlbh,sum(cksl) cksl
26 from tc
27 group by wlbh) c
28 where ta.wlbh=b.wlbh(+)
29 and ta.wlbh=c.wlbh(+)
30 group by ta.wlbh
31 /
WLBH CSSL RKSL CKSL
---------- ---------- ---------- ----------
101 4 3 5
102 0 4 1
201 0 0 5
301 5 5 0
501 6 0 0