select a.a,a.b,a.c,sum(b.x) from A a,B b
where a.a=b.a and a.b=b.b and a.c=b.c
group by a.a,a.b,a.c
如果where条件不满足,如何实现sum(b.x)为0
谢谢
------解决思路----------------------
select a.a,a.b,a.c,nvl(sum(b.x),0) from A a,B b
where a.a=b.a(+) and a.b=b.b(+) and a.c=b.c(+)
group by a.a,a.b,a.c
------解决思路----------------------
select a.a,a.b,a.c,sum(nvl(b.x,0)) from A a,B b
where a.a=b.a and a.b=b.b and a.c=b.c
group by a.a,a.b,a.c
------解决思路----------------------
若把a当作主表,b来匹配a,这种情况,凡是a有数据,b没有与之匹配的数据,此种情况做法为:
SELECT a.a,
a.b,
a.c,
nvl(SUM(b.x), 0)
FROM a a,
b b
WHERE a.a = b.a(+)
AND a.b = b.b(+)
AND a.c = b.c(+)
GROUP BY a.a,
a.b,
a.c
若不满足where条件的意思为同时存在a有b没有,b有a没有的情况,则要复杂点,估计楼主的意思是第一种吧
------解决思路----------------------
SELECT a.a
,a.b
,a.c
,nvl(SUM(b.x),0)
FROM A a
LEFT JOIN B b ON a.a = b.a
AND a.b = b.b
AND a.c = b.c
GROUP BY a.a
,a.b
,a.c
------解决思路----------------------
select a.a,a.b,a.c ,sum(nvl(b.x,0)) from a left join
b on (a.a = b.a and a.b = b.b and a.c = b.c)
group by a.a,a.b,a.c
或者
select t.a,t.b,t.c ,sum(t.x) from (
select a.a , a.b , a.c , 0 x from a
union all
select b.a , b.b , b.c , x from b) t
group by t.a,t.b,t.c