select a.PART_NO,nvl(sum(a.qty),0) t_good_qty,nvl(sum(a.RESERVED_QTY),0)
t_good_reserved_qty,nvl(sum(a1.qty),0) t_bad_qty,nvl(sum(a.RESERVED_QTY),0) t_bad_reserved_qty,nvl(sum(a2.qty),0) t_b_qty
,nvl(sum(a2.RESERVED_QTY),0) t_b_reserved_qty from tab a,tab a1,tab a2 where (a.sic_id=1124 or a.sic_id=1121 )
and (a1.sic_id=1122 or a1.sic_id=1141) and (a2.sic_id=1123) and a.part_no=a1.part_no and a1.part_no=a2.part_no group by a.PART_NO
表tab
part_no sic_id loc_no qty reserved_qty
5001 1121 A11 30 10
5001 1121 A12 20 5
5001 1123 A13 0 0
5001 1124 B00 50 15
t_good_reserved_qty,nvl(sum(a1.qty),0) t_bad_qty,nvl(sum(a.RESERVED_QTY),0) t_bad_reserved_qty,nvl(sum(a2.qty),0) t_b_qty
,nvl(sum(a2.RESERVED_QTY),0) t_b_reserved_qty from tab a,tab a1,tab a2 where (a.sic_id=1124 or a.sic_id=1121 )
and (a1.sic_id=1122 or a1.sic_id=1141) and (a2.sic_id=1123) and a.part_no=a1.part_no and a1.part_no=a2.part_no group by a.PART_NO
表tab
part_no sic_id loc_no qty reserved_qty
5001 1121 A11 30 10
5001 1121 A12 20 5
5001 1123 A13 0 0
5001 1124 B00 50 15
part_no sic_id loc_no qty
5001 1121 A11 30
5001 1121 A12 20
5001 1123 A13 0
5001 1124 B00 100
结果
part_no good_qty bad_qty b_qty
5001 50 100 0
表tab sic_id 为1121的是好库,1124 坏库,1123 报废库,库又细分了储位,我要得的结果是这个商品所有好的,坏的,报废的
part_no sic_id loc_no qty
5001 1121 A11 30
5001 1121 A12 20
----如这里,可能没数据
5001 1124 B00 100
结果
part_no good_qty bad_qty b_qty
5001 50 100 0sic_id 为1121 1124 1123 有可能是没有数据的,所以我sql语句那样写有问题的,得不出数据,实在想不出,求大侠帮哈忙,谢谢
with t1 as
(
select 5001 part_no,1121 sic_id,'A11' loc_no,30 qty from dual
union all
select 5001 part_no,1121 sic_id,'A12' loc_no,20 qty from dual
union all
select 5001 part_no,1124 sic_id,'B00' loc_no,100 qty from dual
)
select part_no,
sum(decode(sic_id,1121,qty,0)) good_qtyj,
sum(decode(sic_id,1124,qty,0)) bad_qty,
sum(decode(sic_id,1123,qty,0)) b_qty
from t1
group by part_no
part_no good_qtyj bad_qty b_qty
-----------------------------------------
1 5001 50 100 0