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

数据准备

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));
Truncate table Student;
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');
Truncate table Department;
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');

需求

编写一个SQL查询，为Department表中的所有部门(甚至是没有当前学生的部门)报告各自的部门名称和每个部门的学生人数。

输入

输出

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