借助case，实现更丰富的分组查询统计

根据fileD6的前4位分组    分别统计该组  5种企业类型fileD31的数量

create or replace view jyjc_bycity as
select
 substr(fileD6,1,4)  site,
count( case when fileD31 like '%国有%' then 1 end) numg,
count( case when fileD31 like '%集体%' then 1 end) numj,
count( case when fileD31 like '%股份%' then 1 end) numm,
count( case when fileD31 like '%海外%' then 1 end) numw,
count( case when fileD31 like '%其他%' then 1 end) numq
from tab_jyjc_data t  group by     substr(fileD6,1,4) order by substr(fileD6,1,4)

case语句对企业类型进行筛选 

结果：

类似问题 还有  根据班级分组分别统计班级内男和女的数量 

 select class ,  count(case when Sex='男' then 1 end) as 男,
                 count(case when Sex='女' then 1 end) as 女 
 from Tab group by class