SQL Case when

统计不同身份角色，注册情况，列横向统计，列纵向统计两种；

一、横向统计

select
count(*) as 总注册用户量,
sum(case TypeId when 2 then 1 else 0 end) as 生产企业,
sum(case TypeId when 3 then 1 else 0 end) as 农机局,
sum(case TypeId when 4 then 1 else 0 end) as 经销商,
sum(case TypeId when 5 then 1 else 0 end) as 合作商,
sum(case TypeId when 6 then 1 else 0 end) as 农机主,
sum(case TypeId when 7 then 1 else 0 end) as 种植户
from Table

 

二、纵向统计
select (case TypeId
when 101 then '总注册用户量'
when 102 then '生产企业'
when 103 then '农机局'
when 104 then '经销商'
when 105 then '合作商'
when 106 then '农机主'
when 107 then '种植户'
else ''
end
) 身份类型,注册用户量
from ( select 1 as TypeId,count(*) as 注册用户量 from Table union
( select TypeId,count(TypeId) as 注册用户量 from Table group by TypeId)) A