You need to enable JavaScript to run this app. MYSQL 5.6 如何查询每个学生每门科目对应的最新成绩记录
优惠活动
大模型
产品
解决方案
定价
更多

MYSQL 5.6 如何查询每个学生每门科目对应的最新成绩记录

需求说明

我需要从学生成绩表中获取每个学生对应每门科目的最新成绩,最新记录的判定规则为:优先取udate字段值最大的条目,同一udate下再取oder字段值最大的条目(同日期下新增记录的oder值会逐次+1)。

样例表结构与测试数据

样例表包含2名学生(Mujeeb、Zakariya)和2门科目(ENGLISH、MATHS)的成绩记录,建表及测试数据如下:

DROP TABLE IF EXISTS `students`;
CREATE TABLE IF NOT EXISTS `students` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `udate` date NOT NULL,
  `oder` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `Subject` varchar(20) NOT NULL,
  `mark` int(11) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

INSERT INTO `students` (`uid`, `udate`, `oder`, `name`, `Subject`, `mark`) VALUES
(1, '2021-08-01', 1, 'Mujeeb', 'ENGLISH', 10),
(2, '2021-08-01', 1, 'Zakariya', 'ENGLISH', 20),
(3, '2021-08-10', 2, 'Mujeeb', 'ENGLISH', 50),
(4, '2021-08-11', 2, 'Zakariya', 'ENGLISH', 60),
(5, '2021-08-02', 1, 'Mujeeb', 'ENGLISH', 100),
(6, '2021-08-03', 1, 'Zakariya', 'ENGLISH', 110),
(7, '2021-08-10', 1, 'Mujeeb', 'ENGLISH', 500),
(8, '2021-08-11', 1, 'Zakariya', 'ENGLISH', 600),
(9, '2021-08-01', 2, 'Mujeeb', 'MATHS', 100),
(10, '2021-08-01', 2, 'Zakariya', 'MATHS', 75),
(11, '2021-08-10', 3, 'Mujeeb', 'MATHS', 50),
(12, '2021-08-11', 3, 'Zakariya', 'MATHS', 60);    
实现方案

方案1:使用窗口函数(适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库)

通过ROW_NUMBER()窗口函数按学生姓名、科目分组,按日期降序、序号降序排序,取每组排名第一的记录即可:

SELECT uid, udate, oder, name, Subject, mark
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY name, Subject ORDER BY udate DESC, oder DESC) AS rn
    FROM students
) t
WHERE rn = 1;

方案2:不使用窗口函数(兼容MySQL 5.x等低版本数据库)

写法1:关联子查询匹配(逻辑更直观)

按学生+科目匹配每组内排序第一的记录:

SELECT DISTINCT s.*
FROM students s
WHERE (s.name, s.Subject, s.udate, s.oder) = (
    SELECT name, Subject, udate, oder
    FROM students s2
    WHERE s2.name = s.name AND s2.Subject = s.Subject
    ORDER BY udate DESC, oder DESC
    LIMIT 1
);

写法2:分层关联查询(性能更优,适合数据量较大的场景)

先查询每个学生+科目的最大日期,再匹配该日期下的最大序号,最后关联原表取完整记录:

SELECT s.*
FROM students s
INNER JOIN (
    SELECT t1.name, t1.Subject, t1.max_udate, MAX(t2.oder) AS max_oder
    FROM (
        SELECT name, Subject, MAX(udate) AS max_udate
        FROM students
        GROUP BY name, Subject
    ) t1
    INNER JOIN students t2 ON t1.name = t2.name 
                           AND t1.Subject = t2.Subject 
                           AND t1.max_udate = t2.udate
    GROUP BY t1.name, t1.Subject, t1.max_udate
) t3 ON s.name = t3.name 
    AND s.Subject = t3.Subject 
    AND s.udate = t3.max_udate 
    AND s.oder = t3.max_oder;
执行结果

两种方案执行后都可以得到预期的4条结果:

uidudateodernameSubjectmark
32021-08-102MujeebENGLISH50
42021-08-112ZakariyaENGLISH60
112021-08-103MujeebMATHS50
122021-08-113ZakariyaMATHS60

内容的提问来源于stack exchange,提问作者Sam

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.09 08:24:02