如何按id_student分组筛选id_desireCollage=5不在前5行的学生ID

需求 #

从student_desire表中，按id_student分组，找出那些id_desireCollage=5不在该学生志愿列表前5行的id_student。

数据表 #

尝试的SQL语句 #

select id_student
from   student_desire
group by(id_student)
having id_desireCollage not in first 5 rows

期望结果 #

正确解法 #

原SQL的问题在于GROUP BY语法无法直接引用行顺序，HAVING子句也不能用first 5 rows这种非标准语法。要实现需求，需要先给每个学生的志愿记录按ID排序并标记行号，再筛选目标记录。

可用SQL（支持窗口函数的数据库：MySQL 8+、PostgreSQL、SQL Server等） #

SELECT DISTINCT id_student
FROM (
    SELECT 
        id_student,
        id_desireCollage,
        ROW_NUMBER() OVER (PARTITION BY id_student ORDER BY ID) AS row_num
    FROM student_desire
) t
WHERE id_desireCollage = 5 AND row_num > 5;

说明 #

1. 子查询里用ROW_NUMBER()窗口函数，按学生ID分组，每组内按记录ID从小到大排序，生成行号row_num，代表该志愿在学生列表里的位置。
2. 外层查询筛选出id_desireCollage=5且行号大于5的记录，用DISTINCT保证每个学生ID只输出一次。
3. 执行后会得到id_student=3，因为该学生的id_desireCollage=5排在第8位，超出前5；而学生1的该志愿在第5位，学生2的在第2位，都不符合条件。

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