case when else end
+----+---+------+
| id|num|gender|
+----+---+------+
|悟空| A| 男|
|娜娜| A| 男|
|宋宋| B| 男|
|凤姐| A| 女|
|热巴| B| 女|
|慧慧| B| 女|
+----+---+------+
//求出不同部门男女各多少人。结果如下:
//A 2 1
//B 1 2
select
num,
sum(case when gender=="男" then 1 else 0 end) as M,
sum(case when gender =="女" then 1 else 0 end )as N
from
v_CaseWen
group by num
第一种方式
sum(case when gender=="男" then 1 else 0 end) as M,
sum(case when gender =="女" then 1 else 0 end )as N
第二种方式
sum(case gender when "男" then 1 else 0 end) as M, //当case 当gender when等于 "男" 的是 then 返回1 else或者 0 end结束
sum(case gender when "女" then 1 else 0 end )as n