MySQL 语句求助？

• Z时代
• 2024-02-29
• 分类：IT

score表

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

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

这样似乎有错误

回答：

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;

可以用窗口函数实现

回答：

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

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 最近一次考试 分数最高的那一条数据”，假如说成绩一样呢。如下数据

学生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

这样就完美了。

回答：

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

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

回答：

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;

以上是 MySQL 语句求助？ 的全部内容， 来源链接： utcz.com/p/944833.html