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

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

selectdept_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_numfrom 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)

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

selectdept_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_numfrom 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='职员表'

现有记录：

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)

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