学生成绩管理系统 MySql语句 查询学生最终成绩(最新成绩)

说明

学生成绩管理一直是学生管理系统中的一个大头，因为重修\补考等机制，所以使用操作命令的格式存储的课程成绩就会由于时间的不同而导致该课程会有多项成绩，但我们期望每个课程的成绩是唯一的，也就是只取最后一次成绩。
假设有数据表格如下表所示：

数据表格

id	stuId	courseId	grade	time
1	1	1	80	2021-4-27
2	1	2	40	2021-4-28
3	1	3	99	2021-4-27
4	1	1	70	2020-4-27
5	1	2	67	2020-4-27
6	1	2	90	2022-4-29

我们期望经过查询后可以获得下表数据。

期望数据

id	stuId	courseId	grade	time
1	1	1	80	2021-4-27
3	1	3	99	2021-4-27
6	1	2	90	2022-4-29

问题解决方案推导

尝试一：查询学生的数据后，再对时间进行倒序排序，再利用Group By

# select * from testinfo where stuId=@stuid ORDER BY time 
select id,stuid,courseId,grade,time from 
(select * from testinfo where stuId=@stuId ORDER BY time desc) as a 
group by courseId;

查询结果

id	stuId	courseId	grade	time
1	1	1	80	2021-4-27
2	1	2	40	2022-4-28
3	1	3	99	2021-4-27

看到第二条的时候，问题出来了，是没有排序直接就分组处理了吗？答案是是的。

group by 和 order by 一起使用时，会先使用group by 分组，并取出分组后的第一条数据，所以后面的order by 排序时根据取出来的第一条数据来排序的，但是第一条数据不一定是分组里面的最大数据¹。

使用文中介绍的方法经过测试后，结果也不是需求目标，应该是不适用于我们想要的问题类型，所以只能转换思路。

尝试二：两表连接查询

其中一个表查询到所有指定学生的成绩记录，另一个表则是使用gourp by查询到id，课程和考试时间三个重要因素即可。

# 查询该学生所有课程
# select * from testinfo where stuId=@stuid;
# 查询该学生每门不同的课程最后的考试时间
# select courseId,max(time) from testinfo where stuId=@stuid group by courseId; 
# 连接两表,查询出最终成绩
select * from(select * from testinfo where stuId=@stuid) a,
(select courseId,max(time) time from testinfo where stuId=@stuid group by courseId) b 
where a.courseId = b.courseId and a.time=b.time;

查询结果

id	stuId	courseId	grade	time
1	1	1	80	2021-4-27
3	1	3	99	2021-4-27
6	1	2	90	2022-4-29

此时则我们获得了期望数据。
但是，由于连接查询的原因，如果数据量过大时，该方法会导致查询速度过慢，不过学生学期的课程一般最多不会超过50门，考试次数不会超过50*4次（对于大学生而言），属于小数据。

分析

总体来说还是解决了查询上的问题，但是效率上并不是特别美好，只是目前对于我的需求是足够的，就没有继续研究效率更好的解决办法。如果有更好的解决办法，还望不吝赐教。

---

1. group by 和 order by 一起使用问题处理 ↩︎