每日sql-统计各个专业人数（包括专业人数为0的）

每日sql-统计各个专业人数（包括专业人数为0的）

DDL

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');

sql

select t2.dept_name,count(t1.student_id) as student_number from student t1 right join department t2 on t1.dept_id = t2.dept_id group by t2.dept_name;