用sql语句进行不同条件的统计

进行不同条件的统计是case表达式著名用法之一，我们统计各个班级男女的性别数量：

如图：班级，性别，人数

我现在统计所有班级男生数量：

select 
sum(person_num) as person_num
from school_room
where sex = '1'

统计所有女生的人数：

select 
sum(person_num) as person_num
from school_room
where sex = '2'

我i们使用case表达式来处理：

select 
 sum(case when sex = '1' THEN person_num else 0 end) as boy_num,
 sum(case when sex = '2' then person_num else 0 end) as giril_num
from school_room

 

我们还可以用if()函数统计：

select 
 sum(IF(sex = '1',person_num,0)) as boy_num,
 sum(IF(sex = '2',person_num,0)) as giril_num
from school_room