Grouping函数使用

最新推荐文章于 2023-03-27 12:03:30 发布

congzhi8603

最新推荐文章于 2023-03-27 12:03:30 发布

阅读量159

点赞数

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 南宁
,()
)
;