需求：按照地區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
'合計' 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
area_code,
group by area_code
order by area_code
2、采用rollup()函數
decode(grouping(area_code),1,'total',area_code) as area_code,