MySQL,where条件中使用case when你会吗？「建议收藏」

欢迎大家来到IT世界,在知识的湖畔探索吧!

查询 101部门salary>3000,102部门salary>4000, 其他部门salary>5000的男生、女生分别多少人？

可见，部门不同，条件也不同，怎么写呢？where条件中使用case when ,如下语句符合期望：

select
dept_id,
count(*) as total_num,
sum(
case 
  when sex = 1 then 1 else 0 end
) as female_num 
,
sum(
case 
  when sex = 0 then 1 else 0 end
) as male_num
,
sum(
case 
  when  (sex<>1 and sex<>0) or sex is null then 1 else 0 end
) as unknown_num
from staff 
where salary > 
(case 
    when dept_id = '101' then 3000
    when dept_id = '102' then 4000
    else 5000
 end)
group by dept_id;

+---------+-----------+------------+----------+-------------+
| dept_id | total_num | female_num | male_num | unknown_num |
+---------+-----------+------------+----------+-------------+
| 101     |         4 |          1 |        2 |           1 |
| 102     |         2 |          1 |        0 |           1 |
| 104     |         1 |          0 |        1 |           0 |
+---------+-----------+------------+----------+-------------+
3 rows in set (0.00 sec)

欢迎大家来到IT世界,在知识的湖畔探索吧!

where条件中使用case when ，使用另一种语法，如下语句也符合期望：

欢迎大家来到IT世界,在知识的湖畔探索吧!select
dept_id,
count(*) as total_num,
sum(
case 
  when sex = 1 then 1 else 0 end
) as female_num 
,
sum(
case 
  when sex = 0 then 1 else 0 end
) as male_num
,
sum(
case 
  when  (sex<>1 and sex<>0) or sex is null then 1 else 0 end
) as unknown_num
from staff 
where -- 换一种case when的语法
(case 
    when dept_id = '101' then salary > 3000
    when dept_id = '102' then salary > 4000
    else salary > 5000
 end)
group by dept_id;

关于case when的用法详见：

MySQL,case when你真的会用吗？附避坑指南

MySQL分组统计你会吗？case when,sum,count会用吗？

表结构如下：

CREATE TABLE `staff` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(50) NOT NULL COMMENT '姓名',
  `dept_id` varchar(10) DEFAULT NULL COMMENT '部门id',
  `salary` double DEFAULT NULL COMMENT '工资',
  `sex` int(11) DEFAULT NULL COMMENT '性别，1是男生，0是女生',
  `created_time` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='职员表'

现有记录：

欢迎大家来到IT世界,在知识的湖畔探索吧!mysql>  select * from staff ;
+----+-------+---------+--------+------+---------------------+---------------------+
| id | name  | dept_id | salary | sex  | created_time        | updated_time        |
+----+-------+---------+--------+------+---------------------+---------------------+
|  1 | Tom   | 101     |   2500 |    1 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |
|  2 | Tonny | 101     |   3500 |    1 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |
|  3 | Bob   | 101     |   3500 |    0 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |
|  4 | Rob   | 101     |   4500 | NULL | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |
|  5 | Sudey | 101     |   5500 |    0 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |
|  6 | Sunny | 102     |   5500 |    1 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |
|  7 | Sedey | 102     |   3500 |    0 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |
|  8 | Bobby | 102     |   4500 | NULL | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |
|  9 | Weedy | 103     |   4500 |    1 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |
| 10 | Jimme | 104     |   5500 |    0 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |
+----+-------+---------+--------+------+---------------------+---------------------+
10 rows in set (0.00 sec)

「欢迎关注，每天更新工作实用技能」