2021-04-16

统计各专业人数

需求：查询 department 表中每个专业的学生人数 （即使没有学生的专业也需列出）。

展示效果：

dept_name	student_number
Engineering	2
Science	1
Law	0

CREATE TABLE IF NOT EXISTS 20_student (student_id INT,student_name VARCHAR(45), gender VARCHAR(6), dept_id INT);
CREATE TABLE IF NOT EXISTS 20_department (dept_id INT, dept_name VARCHAR(255));
Truncate table 20_student;
insert into 20_student (student_id, student_name, gender, dept_id) values (1, 'Jack', 'M', 1);
insert into 20_student (student_id, student_name, gender, dept_id) values (2, 'Jane', 'F', 1);
insert into 20_student (student_id, student_name, gender, dept_id) values (3, 'Mark', 'M', 2);
Truncate table 20_department;
insert into 20_department (dept_id, dept_name) values (1, 'Engineering');
insert into 20_department (dept_id, dept_name) values (2, 'Science');
insert into 20_department (dept_id, dept_name) values (3, 'Law');

最终sql：

select
	dept_name,
	count(student_id) as student_number
from	
	20_department d
     left join
     20_student s
     on 
     d.dept_id = s.dept_id 
     
group by 
	 dept_name
order by 
	count(student_id)  desc