【SQL】用一条SQL语句进行不同条件统计，多条件聚合，case when ,if，sum()，count()

通过select子句进行条件分支，效率高于使用where子句

 city    orderid       sex
 北京   2984798665      男
 上海   7658545679      女
 ...    ...            ...

通过订单号统计各城市的男女性销量

方法1：sum()函数聚合，此类可能会遇到没有去重问题

 select city
  ,sum(case when sex = '男' then '1' else '0' end) as cnt_m
  ,sum(case when sex = '女' then '1' else '0' end) as cnt_f
from table
group by city

等价于方法1：

 select city
  ,sum(if(sex = '男' ,'1','0')) as cnt_m
  ,sum(if(sex = '女' ,'1','0')) as cnt_f
from table
group by city

方法2：count()函数聚合，考虑去重，利用count()函数忽略null值的特性

 select city
  ,count(distinct case when sex = '男' then orderid else null end) as cnt_m
  ,count(distinct case when sex = '女' then orderid else null end) as cnt_f
from table
group by city

等价于方法2：

 select city
  ,count(distinct if(sex = '男' ,orderid ,null )) as cnt_m
  ,count(distinct if(sex = '女' ,orderid ,null )) as cnt_f
from table
group by city