使用子查询筛选各id_student组前5行后的共同id_desireCollage

问题拆解 #

我们需要完成两个核心步骤：

• 给每个学生的志愿按顺序排序，跳过前5个志愿
• 找出所有学生在剩余志愿里都共同选择的院校

你的SQL的问题 #

• 子查询里的ds.id_desireCollage = desires.id_desireCollage是无效的自关联，没有起到任何筛选作用
• 错误地按id_student分组，我们需要统计的是每个院校被多少学生在第6志愿及以后选择，应该按id_desireCollage分组
• having count(*) >5逻辑错误，这里我们需要的是院校被所有学生（数据里共3个）都选中，所以应该判断统计的学生数等于总学生数

正确的SQL实现（使用子查询） #

SELECT id_desireCollage
FROM (
    -- 子查询：给每个学生的志愿排序，标记出前5志愿之外的记录
    SELECT 
        id_desireCollage,
        id_student,
        ROW_NUMBER() OVER (PARTITION BY id_student ORDER BY ID) AS desire_rank
    FROM desires
) AS filtered_desires
WHERE desire_rank > 5  -- 跳过前5个志愿
GROUP BY id_desireCollage
-- 筛选出被所有学生都选中的院校
HAVING COUNT(DISTINCT id_student) = (SELECT COUNT(DISTINCT id_student) FROM desires);

代码解释 #

1. 子查询部分：用ROW_NUMBER()窗口函数，按id_student分组、ID升序排序，给每个学生的志愿标记排名desire_rank（ID越小，志愿越靠前）
2. 筛选剩余志愿：通过desire_rank >5过滤掉每个学生的前5个志愿
3. 统计共同院校：按院校分组，统计有多少不同的学生在剩余志愿里选了该院校，当数量等于总学生数时，就是所有学生都共同选择的院校

结果验证 #

代入你的数据后，最终会得到：

完全符合预期结果。

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