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条结果:
| uid | udate | oder | name | Subject | mark |
|---|---|---|---|---|---|
| 3 | 2021-08-10 | 2 | Mujeeb | ENGLISH | 50 |
| 4 | 2021-08-11 | 2 | Zakariya | ENGLISH | 60 |
| 11 | 2021-08-10 | 3 | Mujeeb | MATHS | 50 |
| 12 | 2021-08-11 | 3 | Zakariya | MATHS | 60 |
内容的提问来源于stack exchange,提问作者Sam
相关产品推荐
相关产品推荐

