580. 统计各专业学生人数（难度：困难）

题目描述

（通过次数19,215 | 提交次数36,291，通过率52.95%）

表:Student
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| student_name | varchar |
| gender       | varchar |
| dept_id      | int     |
+--------------+---------+
Student_id是该表的主键。
dept_id是Department表中dept_id的外键。
该表的每一行都表示学生的姓名、性别和所属系的id。
表:Department
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| dept_id     | int     |
| dept_name   | varchar |
+-------------+---------+
Dept_id是该表的主键。
该表的每一行包含一个部门的id和名称。

编写一个SQL查询，为Department表中的所有部门(甚至是没有当前学生的部门)报告各自的部门名称和每个部门的学生人数。
按 student_number 降序返回结果表。如果是平局，则按 dept_name 的字母顺序排序。

查询结果格式如下所示。
示例 1:
输入: 
Student 表:
+------------+--------------+--------+---------+
| student_id | student_name | gender | dept_id |
+------------+--------------+--------+---------+
| 1          | Jack         | M      | 1       |
| 2          | Jane         | F      | 1       |
| 3          | Mark         | M      | 2       |
+------------+--------------+--------+---------+
Department 表:
+---------+-------------+
| dept_id | dept_name   |
+---------+-------------+
| 1       | Engineering |
| 2       | Science     |
| 3       | Law         |
+---------+-------------+
输出: 
+-------------+----------------+
| dept_name   | student_number |
+-------------+----------------+
| Engineering | 2              |
| Science     | 1              |
| Law         | 0              |
+-------------+----------------+

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/count-student-number-in-departments

//测试数据
Create table If Not Exists Student (student_id int,student_name varchar(45), gender varchar(6), dept_id int);
Create table If Not Exists Department (dept_id int, dept_name varchar(255));

insert into Student (student_id, student_name, gender, dept_id) values ('1', 'Jack', 'M', '1');
insert into Student (student_id, student_name, gender, dept_id) values ('2', 'Jane', 'F', '1');
insert into Student (student_id, student_name, gender, dept_id) values ('3', 'Mark', 'M', '2');

insert into Department (dept_id, dept_name) values ('1', 'Engineering');
insert into Department (dept_id, dept_name) values ('2', 'Science');
insert into Department (dept_id, dept_name) values ('3', 'Law');

解题思路

这道题看着不难，实际上用到的知识点还是挺多的。

**首先**，题目要求返回所有的部门，即使那个部门下没有任何学生。所以在表的连接方式上，需要选用外连接。

**其次**，题目要求统计每个部门下的学生数量。那么，可以使用GROUP BY+COUNT来统计。而又不能使用COUNT(*)或COUNT(1)，因为这样会把没有任何学生的部门下的学生数统计为1。

这里可以使用COUNT(student_id)，这样当关联不到student时，student_id为NULL值。而在计算COUNT时，NULL值是不计算在内的。

**最后**，结果要求以学生数倒序排序返回。

由于SQL各子句的执行顺序中，GROUP BY在ORDER BY之前执行。因此，在ORDER BY子句中，可以使用聚合函数表达式进行排序。

FROM > WHERE > GROUP BY > HAVING > SELECT > DISTINCT > ORDER BY > LIMIT

如此一来，题目要求返回的结果，使用一句SELECT语句，即可全部返回。

参考SQL

未特别说明的情况下，参考SQL为基于MySQL8.0实现。

select
    a.dept_name,
    count(b.student_id) student_number
from Department a
left join Student b
on a.dept_id = b.dept_id
group by a.dept_name
order by count(b.student_id) desc,a.dept_name;