MySQL复杂查询:连接查询+取某个类型的最大值

时间:2021-10-08 14:26:22

本文链接:https://www.cnblogs.com/alanabc/p/10167926.html

需求

假设有一个考试,比如CET(包括CET-4和CET-6),学生可以多次报考刷分。现在某教育单位要从考试结果中把每个学生的CET-6最高分拿出来,然后进行一个排名。

表结构

现在有两个数据表:student 和 exam_result,分别表示学生的基本信息和每次考试结果(包括CET-4和CET-6的结果)。

这里不考虑表结构的优化和完整,只给出必要字段。

student:

字段名 含义
id ID(主键)
name 姓名

MySQL复杂查询:连接查询+取某个类型的最大值

exam_result:

字段名 含义
id ID(主键)
cetVersion CET版本或等级(CET-4还是CET-6)
studentId 学生ID
score 成绩

MySQL复杂查询:连接查询+取某个类型的最大值

分析

step 1

先尝试把两个表关联起来,LEFT JOIN左连接还记得吗?(连接查询不是关联查询)

SELECT exam_result.studentId, student.name, exam_result.cetVersion, exam_result.score
FROM exam_result
LEFT JOIN student ON (exam_result.studentId=student.id)

MySQL复杂查询:连接查询+取某个类型的最大值

step 2

可是我们要的是CET-6的呀,那就在末尾加上 WHERE exam_result.cetVersion=6 吧。千万不要加在FROM后面,语法检查都不给你通过,更不要谈查数据啦。

于是我们就能清晰的看到,只有张三、李四和六神(SixGod)同学参加了六级考试,其中张三还考了两次。

MySQL复杂查询:连接查询+取某个类型的最大值

step 3

a

不忙着排序,这个等拿到想要的数据后再来。

先想想怎么“过滤”一下,把每个人多出来的考试记录去掉,只留下最高分的记录。分开来看,就是怎么去重和怎么取最大值的问题。

如果你在搜索引擎上搜索“SQL去重”的话,可能会给出DISTINCT办法,但在:DISTINCT是严格的按照查询字段去重的,相当于你把查询字段都加入了主键,然后以主键唯一作为条件进行查询。比如:SELECT DISTINCT studentId from exam_result,结果理所应当的是去除了重复的studentId;但是只查询一个studentId字段几乎是毫无意义的,还要加上其他字段:SELECT DISTINCT studentId, score from exam_result,你以为会在studentId去重的基础上加上score字段?NO!结果是:(studentId 不唯一) && ( score 不唯一),SQL语句加括号也没用!

MySQL复杂查询:连接查询+取某个类型的最大值

b

建议采用GROUP BY进行去重。在【step 2】的基础上,再在末尾添加GROUP BY exam_result.studentId就好了:

SELECT exam_result.studentId, student.name, exam_result.cetVersion, exam_result.score as score
FROM exam_result
LEFT JOIN student ON (exam_result.studentId=student.id)
WHERE exam_result.cetVersion=6
GROUP BY exam_result.studentId

MySQL复杂查询:连接查询+取某个类型的最大值

step 4

你应该发现,【step 3】查询出来的结果中,张三的并不是最高成绩。因为默认情况下进行ORDER BY,优先获取的是id在前的记录。

既然我们是要取最高成绩,也就是取score字段的最大值,那么有一个很巧地用法:借用SQL的MAX()方法。

把【step 2】中的exam_result.score换成MAX(exam_result.score) as score,也就是把“查询成绩”换成“查询最好成绩”。

于是我们顺利的拿到了想要的结果:“从考试结果中把每个学生的CET-6最高分拿出来”;还差“然后进行一个排名”,你应该想到用ORDER BY方法了,最终拼拼凑凑弄出了这么个东西,它能“从考试结果中把每个学生的CET-6最高分拿出来,然后进行一个排名”:

SELECT exam_result.studentId, student.name, MAX(exam_result.score) as score
FROM exam_result
LEFT JOIN student ON (exam_result.studentId=student.id)
WHERE exam_result.cetVersion=6
GROUP BY exam_result.studentId
ORDER BY MAX(exam_result.score) DESC

MySQL复杂查询:连接查询+取某个类型的最大值

在这个SQl语句中,其实可以简写很多部分,比如把ORDER BY MAX(exam_result.score)简写成ORDER BY MAX(score),毕竟已经在第一行声明了MAX(exam_result.score) as score

结语

在【step 3】【b】开头,我写了“建议采用GROUP BY进行去重”,为什么是“建议”呢?其实DISTINCT也是可以做到我们需要的效果的,但是相对来说比较繁琐,要先在exam_result表内使用UNION合并两个查询结果,然后再去JOIN连接student表。具体的可以Google关键词【distinct multiple fields】或者【distinct multiple columns】。