mysql - 合并表并按升序获取输出

Question

我有两个这样的表:

student                                   department
id  department_id                      department_id  department_name

5     5                                  5              Computer Science
1     4                                  4              Architecture
3     2                                  1              Mathematics
4     5                                  3              Chemistry
2     4                                  2              Physics

我编写了如下查询并得到了以下结果。

SELECT DEPARTMENTS.DEPT_NAME AS D, STUDENTS.DEPT_ID AS D_ID
FROM STUDENTS 
INNER JOIN DEPARTMENTS
ON STUDENTS.DEPT_ID=DEPARTMENTS.DEPT_ID ;



Computer Science 5
Computer Science 5
Physics 2
Architecture 4
Architecture 4

到这里为止还不错，但我想要类似的东西

Computer Science 2
Architecture 2
Physics 1
Chemistry 0
Mathematics 0

即部门名称，学生人数，其中学生人数按降序排列。

我可以向查询中添加什么？

Answer 1

我会使用 COUNT(*) 并将其作为 ORDER BY 的子查询

SELECT * FROM (
SELECT DEPARTMENTS.DEPT_NAME, COUNT(*) AS num_ofstudents
FROM STUDENTS 
LEFT JOIN DEPARTMENTS
ON STUDENTS.DEPT_ID=DEPARTMENTS.DEPT_ID
GROUP BY Departments.Dept_name
) AS a ORDER BY num_ofstudents

编辑 - 感谢 AaronDietz 指出这一点!您应该将 INNER JOIN 替换为 LEFT JOIN，以便查询包含 [Departments] 中没有任何学生的记录。另外，我不需要包含子查询。

SELECT DEPARTMENTS.DEPT_NAME, COUNT(*) AS num_ofstudents
FROM STUDENTS 
LEFT JOIN DEPARTMENTS
ON STUDENTS.DEPT_ID=DEPARTMENTS.DEPT_ID
GROUP BY Departments.Dept_name
ORDER BY num_ofstudents