编写SQL查询2020年至少有1名学生排名1-3的院校数据

原SQL存在的问题 #

• 误用RANK()窗口函数：你需要的是聚合统计结果（最好排名、符合条件的学生总数），不是对行数据做排名计算
• 过滤条件错误：ranking >=3会筛选出排名第3及更靠后的学生，和需求的1~3名完全相反
• 未做院校分组：查询结果会返回每个符合条件的学生一行数据，不是每个院校一行的预期结果

修正后的SQL #

SELECT
    c.name AS '院校名称',
    MIN(r.ranking) AS '2020年该校学生的最好排名',
    COUNT(DISTINCT s.ID) AS '2020年该校排名在1-3之间的学生数量'
FROM
    Colleges c
    JOIN Students s ON c.ID = s.collegeId
    JOIN Rankings r ON s.ID = r.studentID
WHERE
    r.year = 2020
    AND r.ranking BETWEEN 1 AND 3
GROUP BY
    c.ID, c.name

逻辑说明 #

• 先通过WHERE条件过滤出2020年排名1~3的所有学生记录，自动满足「至少有1名学生符合要求」的筛选条件，无需额外加HAVING判断
• 按院校ID、院校名称分组，保证每个院校仅输出一条结果
• 用MIN(r.ranking)取院校最小的排名数值，就是该校2020年的最好排名（排名数值越小，名次越高）
• 用COUNT(DISTINCT s.ID)统计符合条件的学生数，加DISTINCT是避免同一个学生同一年存在多条排名记录时重复计数

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