如何查询SQL表中选课人数最多的所有部门？

现有表结构及数据 #

enrolled表的结构和数据如下：

需求 #

选出所有选课人数最多的部门，最终结果为仅包含CS和Math的单列数据。

你已经写出了统计各部门选课人数的基础语句：

SELECT dept_id,COUNT(dept_id) as "enrollments" FROM enrolled GROUP BY dept_id;

解决方案 #

要筛选出人数最多的部门，需要处理多个部门并列第一的情况，以下是两种可行的方法：

方法一：嵌套子查询获取最大值 #

通过子查询先统计各部门人数，再找出最大人数值，最后筛选出人数等于该最大值的部门：

SELECT dept_id
FROM (
    SELECT dept_id, COUNT(dept_id) AS enrollments
    FROM enrolled
    GROUP BY dept_id
) AS dept_enrollments
WHERE enrollments = (
    SELECT MAX(enrollments)
    FROM (
        SELECT COUNT(dept_id) AS enrollments
        FROM enrolled
        GROUP BY dept_id
    ) AS max_counts
);

方法二：使用窗口函数（推荐） #

如果你的数据库支持窗口函数（如MySQL 8.0+、PostgreSQL、SQL Server等），可以用RANK()函数直接给部门按人数排名，筛选排名为1的部门：

SELECT dept_id
FROM (
    SELECT dept_id,
           COUNT(dept_id) AS enrollments,
           RANK() OVER(ORDER BY COUNT(dept_id) DESC) AS rnk
    FROM enrolled
    GROUP BY dept_id
) AS ranked_depts
WHERE rnk = 1;

这两种方法都能正确返回CS和Math两个部门，满足需求。

内容的提问来源于stack exchange，提问作者Nandadev R. Menon