LeetCode--580. 統計各專業學生人數

建表

DROP TABLE IF EXISTS student;
CREATE TABLE student
(
student_id INT,
student_name VARCHAR(20),
gender CHAR,
dept_id INT
);
DROP TABLE IF EXISTS department;
CREATE TABLE department
(
dept_id INT,
dept_name VARCHAR(20)
);
INSERT INTO student VALUES(1, 'Jack', 'M', 1);
INSERT INTO student VALUES(2, 'Jane', 'F', 1);
INSERT INTO student VALUES(3, 'Mark', 'M', 2);
INSERT INTO department VALUES(1, 'Engineering');
INSERT INTO department VALUES(2, 'Science');
INSERT INTO department VALUES(3, 'Law');

SELECT d.dept_name dept_name, COUNT(student_id ) student_number
FROM department d LEFT JOIN student s
ON d.dept_id = s.dept_id
GROUP BY d.dept_id
ORDER BY student_number DESC, dept_name