Grouping函式使用

Grouping函式使用

[@more@]

--三級彙總
insert into tb_test
select
$1
,case
when grouping (c.fee_area) = 1 then '0'
when grouping (c.fee_area_name) = 1 then '0'
when grouping (c.area_code) = 1 then c.fee_area
when grouping (c.area_code_name) = 1 then c.fee_area
else c.area_code
end as "organize_id"
,case
when grouping (c.fee_area) = 1 then '9'
when grouping (c.fee_area_name) = 1
then decode (c.fee_area,
'771', '1',
'772', '2',
'775', '3',
'773', '4',
'779', '5',
'774', '6',
'776', '7',
'778', '8',
'999', '99',
'99'
)
when grouping (c.area_code_name) = 1
then decode (c.fee_area,
'771', 'A2199',
'772', 'A2299',
'775', 'A2699',
'773', 'A2399',
'779', 'A2599',
'774', 'A2499',
'776', 'A2899',
'778', 'A2799',
'999', 'A9999',
'99999'
)
else a.area_code
end as "area_code1"
,case
when grouping (c.fee_area) = 1 then '廣西合計'
when grouping (c.fee_area_name) = 1
then decode (c.fee_area,
'771', '南寧',
'772', '柳州',
'775', '玉林',
'773', '桂林',
'779', '北海',
'774', '梧州',
'776', '百色',
'778', '河池',
'999', '不詳',
'不詳'
)
when grouping (c.area_code_name) = 1
then decode (c.fee_area,
'771', '南寧合計',
'772', '柳州合計',
'775', '玉林合計',
'773', '桂林合計',
'779', '北海合計',
'774', '梧州合計',
'776', '百色合計',
'778', '河池合計',
'999', '不詳合計',
'不詳'
)
else c.area_code_name
end as "area_desc"
,sum(group_count1)
,sum(group_count2)
,sum(group_count3)
,sum(group_count4)
,sum(group_count5)
,sum(group_count6)
from tp_test_mid a -- 該表可用任意業務表代替
left outer join hwdic.tb_dic_area_sort c
on a.area_code = c.area_code
group by grouping sets ((c.fee_area,c.fee_area_name,c.area_code,c.area_code_name,a.area_code) -- 2101 2101 南寧西區分公司
,(c.fee_area,c.fee_area_name,a.area_code,c.area_code_name) -- 771 2101 南寧西區分公司
,(c.fee_area,c.fee_area_name) -- 771 2199 南寧合計
,(c.fee_area) -- 0 1 南寧
,()
)
;