建表
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