查询拥有最多最高成绩（如6分）的部门名称

我有三张表：student、department和exam_results，需求是查询所有拥有最多最高成绩（比如6分）的部门名称。

我试过下面的查询，但在示例场景里，有两个部门有6分成绩——Informatics部门有2个6分，Chemistry部门只有1个6分，这个查询会返回Chemistry部门，但正确结果应该只返回Informatics部门；如果Chemistry部门也有2个6分，就应该同时返回两个部门。

SELECT department
FROM (SELECT d.department_name as department, count(e_r.grade) as cnt
      FROM exam_results e_r
      INNER JOIN students s ON e_r.student_id = s.student_id
      INNER JOIN department d ON s.department_id = d.department_id
      WHERE e_r.grade = 6
      GROUP BY d.department_name
     ) as ex;

另外，我用下面的查询能获取部门名称及指定成绩（WHERE子句中的n）的数量，但还是没法实现预期需求：

SELECT department_name, max(cnt) as cnt
FROM (SELECT d.department_name as department_name, e_r.grade, count(e_r.grade) as cnt
      FROM exam_results e_r
      INNER JOIN students s ON e_r.student_id = s.student_id
      INNER JOIN department d ON s.department_id = d.department_id
      WHERE grade = 6
      GROUP BY d.department_name, e_r.grade
     ) AS ex
GROUP BY department_name;

示例表结构 #

Student表 #

Department表 #

Exam_results表 #

解决方案 #

方法1：子查询筛选最大值 #

先统计各部门的目标成绩数量，再找出其中的最大值，最后筛选出数量等于最大值的部门：

SELECT department
FROM (
    SELECT d.department_name as department, COUNT(e_r.grade) as cnt
    FROM exam_results e_r
    INNER JOIN students s ON e_r.student_id = s.student_id
    INNER JOIN department d ON s.department_id = d.department_id
    WHERE e_r.grade = 6
    GROUP BY d.department_name
) AS dept_counts
WHERE cnt = (
    SELECT MAX(cnt)
    FROM (
        SELECT COUNT(e_r.grade) as cnt
        FROM exam_results e_r
        INNER JOIN students s ON e_r.student_id = s.student_id
        INNER JOIN department d ON s.department_id = d.department_id
        WHERE e_r.grade = 6
        GROUP BY d.department_name
    ) AS max_counts
);

方法2：窗口函数（更简洁） #

使用RANK()窗口函数给各部门的成绩数量排名，排名为1的就是拥有最多目标成绩的部门：

SELECT department
FROM (
    SELECT 
        d.department_name as department,
        COUNT(e_r.grade) as cnt,
        RANK() OVER(ORDER BY COUNT(e_r.grade) DESC) as rnk
    FROM exam_results e_r
    INNER JOIN students s ON e_r.student_id = s.student_id
    INNER JOIN department d ON s.department_id = d.department_id
    WHERE e_r.grade = 6
    GROUP BY d.department_name
) AS ranked_depts
WHERE rnk = 1;

如果有多个部门的目标成绩数量并列最高，两种方法都会返回所有符合条件的部门。

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