MySQL复杂查询:连接查询+取某个类型的最大值
需求
假设有一个考试，比如CET（包括CET-4和CET-6），学生可以多次报考刷分。现在某教育单位要从考试结果中把每个学生的CET-6最高分拿出来，然后进行一个排名。
表结构
现在有两个数据表：student 和 exam_result，分别表示学生的基本信息和每次考试结果（包括CET-4和CET-6的结果）。
这里不考虑表结构的优化和完整，只给出必要字段。
student：
| 字段名 | 含义 |
|---|---|
| id | ID(主键) |
| name | 姓名 |
exam_result：
| 字段名 | 含义 |
|---|---|
| id | ID(主键) |
| cetVersion | CET版本或等级（CET-4还是CET-6） |
| studentId | 学生ID |
| score | 成绩 |
分析
step 1
先尝试把两个表关联起来，LEFT JOIN左连接还记得吗？（连接查询不是关联查询）
SELECT exam_result.studentId, student.name, exam_result.cetVersion, exam_result.scoreFROM exam_resultLEFT JOIN student ON (exam_result.studentId=student.id)
step 2
可是我们要的是CET-6的呀，那就在末尾加上 WHERE exam_result.cetVersion=6 吧。千万不要加在FROM后面，语法检查都不给你通过，更不要谈查数据啦。
于是我们就能清晰的看到，只有张三、李四和六神（SixGod）同学参加了六级考试，其中张三还考了两次。
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语句加括号也没用！
b
建议采用GROUP BY进行去重。在【step 2】的基础上，再在末尾添加GROUP BY exam_result.studentId就好了：
SELECT exam_result.studentId, student.name, exam_result.cetVersion, exam_result.score as scoreFROM exam_resultLEFT JOIN student ON (exam_result.studentId=student.id)WHERE exam_result.cetVersion=6GROUP BY exam_result.studentId
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 scoreFROM exam_resultLEFT JOIN student ON (exam_result.studentId=student.id)WHERE exam_result.cetVersion=6GROUP BY exam_result.studentIdORDER BY MAX(exam_result.score) DESC
在这个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】。