LeetCode--580. 统计各专业学生人数

1 题目描述

表: Student

+--------------+---------+  
| Column Name  | Type    |  
+--------------+---------+  
| student_id   | int     |  
| student_name | varchar |  
| gender       | varchar |  
| dept_id      | int     |  
+--------------+---------+  
1
2
3
4
5
6
7
8

student_id 是该表的主键 (具有唯一值的列)
dept_id 是 Department 表中 dept_id 的外键
该表的每一行都表示学生的姓名, 性别和所属系的 id

表: Department

+-------------+---------+  
| Column Name | Type    |  
+-------------+---------+  
| dept_id     | int     |  
| dept_name   | varchar |  
+-------------+---------+  
1
2
3
4
5
6

dept_id 是该表的主键 (具有唯一值的列)
该表的每一行包含一个部门的 id 和名称
编写解决方案, 为 Department 表中的所有部门 (甚至是没有当前学生的部门) 报告各自的部门名称和每个部门的学生人数
按 student_number 降序 返回结果表. 如果是平局, 则按 dept_name 的 字母顺序 排序

2 测试用例

输入:
Student 表:

+------------+--------------+--------+---------+  
| student_id | student_name | gender | dept_id |  
+------------+--------------+--------+---------+  
| 1          | Jack         | M      | 1       |  
| 2          | Jane         | F      | 1       |  
| 3          | Mark         | M      | 2       |  
+------------+--------------+--------+---------+  
1
2
3
4
5
6
7

Department 表:

+---------+-------------+  
| dept_id | dept_name   |  
+---------+-------------+  
| 1       | Engineering |  
| 2       | Science     |  
| 3       | Law         |  
+---------+-------------+  
1
2
3
4
5
6
7

输出:

+-------------+----------------+  
| dept_name   | student_number |  
+-------------+----------------+  
| Engineering | 2              |  
| Science     | 1              |  
| Law         | 0              |  
+-------------+----------------+  
1
2
3
4
5
6
7

3 解题思路

1. 用 Department 左外连接 Student , 按照 dept_name 分组统计学生数量

select d.dept_name, count(s.student_id) as student_number  
from Department as d  
         left join Student as s on d.dept_id = s.dept_id  
group by d.dept_name;  
1
2
3
4

2. 对 student_number 倒序排序, 如果是平局, 则按 dept_name 的字母顺序排序

select d.dept_name, count(s.student_id) as student_number  
from Department as d  
         left join Student as s on d.dept_id = s.dept_id  
group by d.dept_name  
order by student_number desc, d.dept_name;  
1
2
3
4
5

查询结果

+-----------+--------------+  
|dept_name  |student_number|  
+-----------+--------------+  
|Engineering|2             |  
|Science    |1             |  
|Law        |0             |  
+-----------+--------------+  
1
2
3
4
5
6
7