如何编写SQL查询获取每个Student_id对应最高mark的整行记录

基础信息 #

现有学生成绩表存量数据如下：

需求为输出每个Student_id最高mark对应的完整行数据，预期结果：

可直接使用的查询语句 #

1. 窗口函数写法（推荐，适配支持SQL:2003标准的数据库：MySQL 8.0+、PostgreSQL、SQL Server、Oracle等） #

SELECT Student_id, mark, semester
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY Student_id ORDER BY mark DESC) AS score_rank
    FROM `你的成绩表实际表名`
) tmp
WHERE score_rank = 1;

说明：如果同一学生存在多个学期分数并列最高的场景，需要返回所有并列最高分行时，将ROW_NUMBER()替换为RANK()即可。

2. 子查询关联写法（兼容老版本数据库，如MySQL 5.x） #

SELECT t1.*
FROM `你的成绩表实际表名` t1
JOIN (
    SELECT Student_id, MAX(mark) AS highest_mark
    FROM `你的成绩表实际表名`
    GROUP BY Student_id
) t2
ON t1.Student_id = t2.Student_id AND t1.mark = t2.highest_mark;

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