图解面试题：如何分组比较？

【题目】

现在有三个表，“学生表”，“课程表”，“成绩表”。

“学生表” 记录了学生的基本信息，有 “学号”、“姓名”、“出生日期”、“性别”。

“成绩表”记录了学生选修课程的成绩，包括 “学号”，选修的“课程号” 以及对应课程的“成绩”。

“课程表” 记录了学生选修的课程信息，包括课程号、课程及其对应的 “老师号”

“学生表”和 “成绩表” 通过 “学号” 联结，“成绩表”和 “课程表” 通过 “课程编号” 联结。

现在要查找出每门课程中成绩最好的学生的姓名和该学生的课程及成绩。

需要注意：可能出现并列第一的情况。

【解题思路】

1. 确定查询结果

题目要求每门课程中成绩最好的学生的姓名和该学生的课程及成绩。可以知道查询结果是：

select 姓名, 课程名称, 成绩

查询结果的 “姓名” 在“学生表”里，“课程名称”在 “课程表” 里，“成绩”在 “成绩表” 里。这里涉及到了 3 个表，所以需要进行多表查询。

2. 如何进行多表查询？

涉及到多表查询，在之前的课程《从零学会 sql：多表查询》里讲过需要用到多表联结。

涉及到多表联结，要考虑清楚两个问题：哪种联结类型？如何联结？

1）哪种联结类型？

多表的联结又分为以下几种类型：

1）左联结（left join），联结结果保留左表的全部数据
2）右联结（right join），联结结果保留右表的全部数据
3）内联结（inner join），取两表的公共数据

其中 “成绩表” 作为 3 个表联结的中间桥梁，所以要以 “成绩表” 表进行左联结，保留左边表（成绩表）里的全部数据。

2）如何联结？

“学生信息表”和 “成绩表” 都有“学号”，所以联结条件为学号。

on a. 学号 = c. 学号

“课程表”和 “成绩表” 都有“课程号”，所以联结条件为课程号。

on a. 课程号 = b. 课程号

多表联结的 sql 如下：

from 成绩表 a
left join 课程表 b on a.课程号=b.课程号
left join 学生信息表 c on a.学号=c.学号

3）多表联结最终 sql

结合前面两步的 sql 如下：

select c.学号,c.姓名,b.课程,a.成绩
from 成绩表 a
left join 课程表 b on a.课程号=b.课程号
left join 学生信息表 c on a.学号=c.学号

运行结果如下：

3. 确定查询条件

题目要求的查询条件是：每门课程中成绩最高的学生。

我们来看这句话里的关键词：

1）“每门课程”，每当出现 “每” 就是要用分组汇总了，所以是子句（group by 课程号）
2）“成绩最好” ，就是最大成绩了，所以是 max(成绩)

因此，查询条件是：

select 课程号,max(成绩) 
from 成绩表 
group by 课程号;

4. 合并前面的步骤，确定最终查询

将第 3 步的查询条件，放到第 2 步多表联结的结果中用于筛选出符合条件的数据。所以查询条件如下：

where (课程号,成绩) in
(select 课程号,max(成绩) 
from 成绩表 
group by 课程号);

需要注意的是，当两列同时作为关键字段进行条件查询时，比如这个案例里是 (课程号, 成绩) in，是将两列合成一个值来查找。比如，“语文” 和“90”合并为值“语文 90”。

所以这两列的顺序要和子查询里列的顺序保持一致。如果列的段顺序不一样，比如 “90 语文” 和 “语文 90” 就匹配不上，那么查询结果就是空的了。

最终 sql：

select c.学号,c.姓名,b.课程,a.成绩
from 成绩表 a
left join 课程表 b on a.课程号=b.课程号
left join 学生表 c on a.学号=c.学号
where (a.课程号,a.成绩) in
(select 课程号,max(成绩) from 成绩表 group by 课程号);

【本题考点】

1）考察三个表的联结，遇到多表查询要想起这个图。

2）考察子查询。当一个查询是另一个查询的条件时则需要用到子查询。

3）考察分组汇总，当题目中出现 “每个” 这样的词，要想起用分组汇总来实现。

4）考察逻辑思维能力，如何将题目复杂的表达拆解成一个一个子问题，这方面可以学习《猴子分析方法》课程里的 “逻辑树分析方法” 来提高。

5）这类型题目属于在 “每个组里比较”，还可以使用“关联子查询” 来做，

【举一反三】

有两个表，Employee 表用于记录员工的薪水和在哪个部门，包括员工的 Id、员工的姓名（Name）、薪水（Salary）和员工所在部门 Id（Department Id）。

Department 表用于记录公司所有部门的信息，包括部门 Id，部门名称（Name）。

找出每个部门工资最高的员工。例如，根据上述给定的表格，员工（Max）在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

思路：

查询结果包括了两个表的列名，所以要进行多表联结。

1）两表联结

如何联结？

因为要查的是所有员工，所以是以员工表（表名 Employee）进行左联结。

联结条件是什么？通过部门 Id 联结。

所以多表联结的 sql 如下：

from Employee
left join Department on Employee.DepartmentId = Department.Id

2）找出每个部门内最高的工资作为子查询从而确定查询的条件

where (Employee.DepartmentId,Salary) in
(select DepartmentId,max(Salary) 
from Employee 
group by DepartmentId)

3）最终 sql 如下

select Department.name,Employee.name,Salary
from Employee
left join Department on Employee.DepartmentId = Department.Id
where (Employee.DepartmentId,Salary) in
(select DepartmentId,max(Salary) 
from Employee 
group by DepartmentId);