如何通过嵌套子查询找出学生最多的院系（含并列处理）

现有student表结构及数据如下：

需求：找出学生人数最多的院系；若有多个院系人数相同，需输出字母顺序更小的院系名称。

原SQL错误分析 #

你写出的SQL语句存在两处核心问题：

SELECT sub.dept_name, max_dep
FROM (SELECT student.dept_name, COUNT(student.dept_name) as dep_count
      FROM student GROUP BY student.dept_name) as sub 
WHERE sub.max_dep = (select max(dep_count) from sub )

1. 字段不存在：max_dep并非子查询sub中的字段，sub仅包含dept_name和dep_count（院系人数统计）两个字段。
2. 临时表引用限制：SQL标准不允许在WHERE子查询中直接引用外层的临时表sub，必须重新计算最大人数值。

正确解决方案（无ORDER BY + LIMIT） #

方案1：嵌套子查询筛选 #

SELECT d1.dept_name
FROM (
    SELECT dept_name, COUNT(*) AS dep_count
    FROM student
    GROUP BY dept_name
) AS d1
-- 筛选出人数等于最大人数的院系
WHERE d1.dep_count = (
    SELECT MAX(dep_count)
    FROM (
        SELECT COUNT(*) AS dep_count
        FROM student
        GROUP BY dept_name
    ) AS d2
)
-- 同时筛选出这些院系中字母顺序最小的
AND d1.dept_name = (
    SELECT MIN(dept_name)
    FROM (
        SELECT dept_name, COUNT(*) AS dep_count
        FROM student
        GROUP BY dept_name
        HAVING COUNT(*) = (
            SELECT MAX(dep_count)
            FROM (
                SELECT COUNT(*) AS dep_count
                FROM student
                GROUP BY dept_name
            ) AS d3
        )
    ) AS d4
);

方案2：使用ALL关键字简化 #

SELECT dept_name
FROM student
GROUP BY dept_name
HAVING COUNT(*) = (
    SELECT MAX(dep_count)
    FROM (
        SELECT COUNT(*) AS dep_count
        FROM student
        GROUP BY dept_name
    ) AS counts
)
AND dept_name <= ALL (
    SELECT dept_name
    FROM student
    GROUP BY dept_name
    HAVING COUNT(*) = (
        SELECT MAX(dep_count)
        FROM (
            SELECT COUNT(*) AS dep_count
            FROM student
            GROUP BY dept_name
        ) AS counts
    )
);

这两个方案都通过纯子查询实现需求，不需要依赖ORDER BY和LIMIT语法。

内容的提问来源于stack exchange，提问作者Jhon Silver