需求：按照地区code分组求个数，并包括合计，先解释下rollup()函数，比group by 多一个总计
select
area_code,
sum(CASE WHEN owner_type = 0 THEN 1 ELSE 0 END) as legalPersonNum,
sum(CASE WHEN owner_type = 1 THEN 1 ELSE 0 END) as adminNum
from licence_manager.business_licence_phone_entity
where area_code is not null and area_code != ''
group by rollup(area_code)
1、第一种，采用union
select
'合计' as area_code,sum(legalPersonNum) as legalPersonNum,
sum(adminNum) as adminNum
from(
select
area_code,
sum(CASE WHEN owner_type = 0 THEN 1 ELSE 0 END) as legalPersonNum,
sum(CASE WHEN owner_type = 1 THEN 1 ELSE 0 END) as adminNum
from licence_manager.business_licence_phone_entity
where area_code is not null and area_code != ''
group by area_code
order by area_code
)
union
select
area_code,
sum(CASE WHEN owner_type = 0 THEN 1 ELSE 0 END) as legalPersonNum,
sum(CASE WHEN owner_type = 1 THEN 1 ELSE 0 END) as adminNum
from licence_manager.business_licence_phone_entity
where area_code is not null and area_code != ''
group by area_code
order by area_code
2、采用rollup()函数
select
decode(grouping(area_code),1,'total',area_code) as area_code,
sum(CASE WHEN owner_type = 0 THEN 1 ELSE 0 END) as legalPersonNum,
sum(CASE WHEN owner_type = 1 THEN 1 ELSE 0 END) as adminNum
from licence_manager.business_licence_phone_entity
where area_code is not null and area_code != ''
group by rollup(area_code)
order by area_code
