with A as(
select 1 aid,'001' partcode,'70' status from dual
union all
select 2 aid,'002' partcode,'10' status from dual
union all
select 3 aid,'003' partcode,'50' status from dual
),
B as(
select 1 bid,'001' partcode,'b001' cgid,2.2 price from dual
union all
select 2 bid,'002' partcode,'b002' cgid,3.0 price from dual
union all
select 3 bid,'003' partcode,'b003' cgid,1.0 price from dual
),
C as(
select 1 cid,'b001' cgid,20 qty,'70' flag from dual
union all
select 2 cid,'b002' cgid,10 qty,'70' flag from dual
union all
select 3 cid,'b004' cgid,10 qty,'70' flag from dual
union all
select 4 cid,'b005' cgid,30 qty,'70' flag from dual
union all
select 5 cid,'b006' cgid,40 qty,'10' flag from dual
union all
select 6 cid,'b007' cgid,40 qty,'70' flag from dual
)
A表,B表位主子表,由partcode关联
B表和C表有cgid左关联,
现查询A表里status=70和C表里flag=70的数据想要的结果:cgid flag price*qty
b001 70 44(A表status=70)
b002 70 0(A表status=10)
b004 70 0(B表无对应cgid记录)
b005 70 0(B表无对应cgid记录)
b007 70 0 (B表无对应cgid记录)
为什么没有b003和b006呢?
b003因为C表无记录
b006因为C表flag=10
select 1 aid,'001' partcode,'70' status from dual
union all
select 2 aid,'002' partcode,'10' status from dual
union all
select 3 aid,'003' partcode,'50' status from dual
),
B as(
select 1 bid,'001' partcode,'b001' cgid,2.2 price from dual
union all
select 2 bid,'002' partcode,'b002' cgid,3.0 price from dual
union all
select 3 bid,'003' partcode,'b003' cgid,1.0 price from dual
),
C as(
select 1 cid,'b001' cgid,20 qty,'70' flag from dual
union all
select 2 cid,'b002' cgid,10 qty,'70' flag from dual
union all
select 3 cid,'b004' cgid,10 qty,'70' flag from dual
union all
select 4 cid,'b005' cgid,30 qty,'70' flag from dual
union all
select 5 cid,'b006' cgid,40 qty,'10' flag from dual
union all
select 6 cid,'b007' cgid,40 qty,'70' flag from dual
)
A表,B表位主子表,由partcode关联
B表和C表有cgid左关联,
现查询A表里status=70和C表里flag=70的数据想要的结果:cgid flag price*qty
b001 70 44(A表status=70)
b002 70 0(A表status=10)
b004 70 0(B表无对应cgid记录)
b005 70 0(B表无对应cgid记录)
b007 70 0 (B表无对应cgid记录)
为什么没有b003和b006呢?
b003因为C表无记录
b006因为C表flag=10
where a.partcode = b.partcode
and A.status=70
and C.flag=70
and b.cgid = c.cgid(+)
union
select cgid,flag,0 from c where
cgid not in (select c.cgid from a,b,c
where a.partcode = b.partcode
and A.status=70
and C.flag=70
and b.cgid = c.cgid(+));
---- ---- ----------
b001 70 44
b002 70 0
b004 70 0
b005 70 0
b006 10 0
b007 70 0
6 rows selected
select c.cgid,flag,price*qty from a,b,c
where a.partcode = b.partcode
and A.status=70
and C.flag=70
and b.cgid = c.cgid(+)
union
select cgid,flag,0 from c where
cgid not in (select c.cgid from a,b,c
where a.partcode = b.partcode
and A.status=70
and C.flag=70
and b.cgid = c.cgid(+))
and flag = '70';这个才是你要的结果