MySQL 语句求助？

Question

score表

id	student_id	score	date
1	1	80	20221118
2	2	88	20221118
3	1	78	20221118
4	2	98	20221118
5	3	89	20221117
6	2	72	20221117
7	1	63	20221117
8	3	81	20221117
7	1	67	20221116
8	2	86	20221116

求查每个 student_id 最近一次考试 分数最高的那一条数据

select * from score group by student_id having date = max(date);

这样似乎有错误

Answer 1

select id, student_id, score, date 
from (select *, 
    row_number() over (partition by student_id order by score, date desc) as ranking
from score) as tmp 
where ranking = 1;

可以用窗口函数实现

Answer 2

通过子查询解决，思路如下：

1、子查询先查出每个学生的最高分

SELECT student_id, MAX( score ) max_score FROM `score` GROUP BY student_id

2、 原表关联子查询的student_id和score，取出每个用户的最高分

SELECT
    s.student_id,
    s.score,
    s.date
FROM
    `score` s
    INNER JOIN ( 
        SELECT student_id, MAX( score ) max_score FROM `score` GROUP BY student_id 
    ) t ON s.student_id = t.student_id AND s.score = t.max_score

---

理论上来说到这应该结束了，但是注意到题主表达有一点“求查每个 student_id 最近一次考试 分数最高的那一条数据”，假如说成绩一样呢。如下数据

id	student_id	score	date
1	1	80	20221115
2	2	70	20221115
2	3	90	20221115
2	1	80	20221118
2	2	80	20221118

学生1在20221115和20221118都考出了80分的成绩，如果按照上述的SQL，查询出来的结果是日期为20221115对应的数据，而且会造成查出来两条数据。所以还需要对student_id和date再进行一次处理：

1. 对学生id分组去重
2. 取出最大的日期

如下

SELECT
    s.student_id,
    s.score,
    MAX(s.date)
FROM
    `score` s
    INNER JOIN ( 
        SELECT student_id, MAX( score ) max_score FROM `score` GROUP BY student_id 
    ) t ON s.student_id = t.student_id AND s.score = t.max_score
GROUP BY s.student_id

这样就完美了。

Answer 3

SELECT * from score WHERE date = (SELECT MAX(date) FROM score) ORDER BY score desc limit 1

很简单，使用子查询，然后使用order by score降序排列,通过limit 1取第一条数据，就是最近一次考试，分数最高的那一条数据

Answer 4

SELECT
    t4.id,
    t4.student_id,
    t4.score,
    t4.date
FROM
    test AS t4
JOIN(
    SELECT
        t1.student_id,
        t1.date,
        MAX(t1.score) AS maxScore
    FROM
        test AS t1
    LEFT JOIN test AS t2 ON
        t1.student_id = t2.student_id
        AND t1.date < t2.date
    WHERE 
        t2.id IS NULL
    GROUP BY
        t1.student_id,
        t1.date) AS t3 ON
    t3.student_id = t4.student_id
    AND t3.date = t4.date
    AND t3.maxScore = t4.score
    LIMIT 0, 10;