报表统计sql语句（decode用法及在sql中巧妙组建map的key）

/* Formatted on 2011/04/19 20:40 (Formatter Plus v4.8.6) */
SELECT bill_type billtype, query_type querytype,
materiel_type_id materieltypeid, z0000 z0000, z0731 z0731,
z0733 z0733, z0732 z0732, z0734 z0734, z0737 z0737, z0730 z0730,
z0736 z0736, z0744 z0744, z0746 z0746, z0745 z0745, z0738 z0738,
z0739 z0739, z0735 z0735, z0743 z0743
FROM (SELECT '0' query_type, v.bill_type, v.materiel_type_id,
SUM (DECODE (DECODE (v.area_id, '0000', v.materiel_num),
'', 0,
v.materiel_num
)
) z0000,
SUM (DECODE (DECODE (v.area_id, '0731', v.materiel_num),
'', 0,
v.materiel_num
)
) z0731,
SUM (DECODE (DECODE (v.area_id, '0733', v.materiel_num),
'', 0,
v.materiel_num
)
) z0733,
SUM (DECODE (DECODE (v.area_id, '0732', v.materiel_num),
'', 0,
v.materiel_num
)
) z0732,
SUM (DECODE (DECODE (v.area_id, '0734', v.materiel_num),
'', 0,
v.materiel_num
)
) z0734,
SUM (DECODE (DECODE (v.area_id, '0737', v.materiel_num),
'', 0,
v.materiel_num
)
) z0737,
SUM (DECODE (DECODE (v.area_id, '0730', v.materiel_num),
'', 0,
v.materiel_num
)
) z0730,
SUM (DECODE (DECODE (v.area_id, '0736', v.materiel_num),
'', 0,
v.materiel_num
)
) z0736,
SUM (DECODE (DECODE (v.area_id, '0744', v.materiel_num),
'', 0,
v.materiel_num
)
) z0744,
SUM (DECODE (DECODE (v.area_id, '0746', v.materiel_num),
'', 0,
v.materiel_num
)
) z0746,
SUM (DECODE (DECODE (v.area_id, '0745', v.materiel_num),
'', 0,
v.materiel_num
)
) z0745,
SUM (DECODE (DECODE (v.area_id, '0738', v.materiel_num),
'', 0,
v.materiel_num
)
) z0738,
SUM (DECODE (DECODE (v.area_id, '0739', v.materiel_num),
'', 0,
v.materiel_num
)
) z0739,
SUM (DECODE (DECODE (v.area_id, '0735', v.materiel_num),
'', 0,
v.materiel_num
)
) z0735,
SUM (DECODE (DECODE (v.area_id, '0743', v.materiel_num),
'', 0,
v.materiel_num
)
) z0743
FROM v_inout_month_report v
WHERE 1 = 1
AND ( v.districtid IN (
SELECT man_obj_id
FROM td_sge_keeping_ref
WHERE keeping_id = 'csck1'
AND manager_level = 1)
OR v.storehouse_id IN (
SELECT man_obj_id
FROM td_sge_keeping_ref
WHERE keeping_id = 'csck1'
AND manager_level = 0)
)
GROUP BY v.materiel_type_id, v.bill_type
UNION
SELECT '1' query_type, bill_type, materiel_type_id,
SUM (DECODE (a.area_id, '0000', 1, 0)) z0000,
SUM (DECODE (a.area_id, '0731', 1, 0)) z0731,
SUM (DECODE (a.area_id, '0733', 1, 0)) z0733,
SUM (DECODE (a.area_id, '0732', 1, 0)) z0732,
SUM (DECODE (a.area_id, '0734', 1, 0)) z0734,
SUM (DECODE (a.area_id, '0737', 1, 0)) z0737,
SUM (DECODE (a.area_id, '0730', 1, 0)) z0730,
SUM (DECODE (a.area_id, '0736', 1, 0)) z0736,
SUM (DECODE (a.area_id, '0744', 1, 0)) z0744,
SUM (DECODE (a.area_id, '0746', 1, 0)) z0746,
SUM (DECODE (a.area_id, '0745', 1, 0)) z0745,
SUM (DECODE (a.area_id, '0738', 1, 0)) z0738,
SUM (DECODE (a.area_id, '0739', 1, 0)) z0739,
SUM (DECODE (a.area_id, '0735', 1, 0)) z0735,
SUM (DECODE (a.area_id, '0743', 1, 0)) z0743
FROM (SELECT DISTINCT v.area_id, v.bill_type, v.bill_id,
v.materiel_type_id
FROM v_inout_month_report v
WHERE 1 = 1
AND ( v.districtid IN (
SELECT man_obj_id
FROM td_sge_keeping_ref
WHERE keeping_id = 'csck1'
AND manager_level = 1)
OR v.storehouse_id IN (
SELECT man_obj_id
FROM td_sge_keeping_ref
WHERE keeping_id = 'csck1'
AND manager_level = 0)
)) a
GROUP BY a.bill_type, materiel_type_id
UNION
SELECT '2' query_type, bill_type, materiel_type_id,
SUM (DECODE (a.area_id, '0000', 1, 0)) z0000,
SUM (DECODE (a.area_id, '0731', 1, 0)) z0731,
SUM (DECODE (a.area_id, '0733', 1, 0)) z0733,
SUM (DECODE (a.area_id, '0732', 1, 0)) z0732,
SUM (DECODE (a.area_id, '0734', 1, 0)) z0734,
SUM (DECODE (a.area_id, '0737', 1, 0)) z0737,
SUM (DECODE (a.area_id, '0730', 1, 0)) z0730,
SUM (DECODE (a.area_id, '0736', 1, 0)) z0736,
SUM (DECODE (a.area_id, '0744', 1, 0)) z0744,
SUM (DECODE (a.area_id, '0746', 1, 0)) z0746,
SUM (DECODE (a.area_id, '0745', 1, 0)) z0745,
SUM (DECODE (a.area_id, '0738', 1, 0)) z0738,
SUM (DECODE (a.area_id, '0739', 1, 0)) z0739,
SUM (DECODE (a.area_id, '0735', 1, 0)) z0735,
SUM (DECODE (a.area_id, '0743', 1, 0)) z0743
FROM (SELECT DISTINCT v.materiel_id, v.materiel_type_id,
v.area_id, v.bill_type
FROM v_inout_month_report v
WHERE 1 = 1
AND ( v.districtid IN (
SELECT man_obj_id
FROM td_sge_keeping_ref
WHERE keeping_id = 'csck1'
AND manager_level = 1)
OR v.storehouse_id IN (
SELECT man_obj_id
FROM td_sge_keeping_ref
WHERE keeping_id = 'csck1'
AND manager_level = 0)
)) a
GROUP BY materiel_type_id, bill_type)
ORDER BY bill_type, query_type, materiel_type_id