SQL图解面试题：如何分组比较？（窗口函数、group by）

SQL图解面试题：如何分组比较？（窗口函数、group by）

【题目】

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

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

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

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

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

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

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

【解题思路】

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 成绩表 aleft join 课程表 b on a.课程号=b.课程号left join 学生信息表 c on a.学号=c.学号

3）多表联结最终sql

结合前面两步的sql如下：

select c.学号,c.姓名,b.课程,a.成绩from 成绩表 aleft 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 成绩表 aleft join 课程表 b on a.课程号=b.课程号left join 学生表 c on a.学号=c.学号where (a.课程号,a.成绩) in(select 课程号,max(成绩) from 成绩表 group by 课程号);

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

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

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

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

【举一反三】

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

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

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

思路：

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

1）两表联结

如何联结？

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

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

所以多表联结的sql如下：

from Employeeleft 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,Salaryfrom Employeeleft join Department on Employee.DepartmentId = Department.Idwhere (Employee.DepartmentId,Salary) in(select DepartmentId,max(Salary) from Employee group by DepartmentId);

第二种方法:

详情可参考往期文章​​SQL图解面试题：用户登录平台分析（分组汇总、连表）_Begin to change的博客-C​​

因为涉及到的是最大值，也就是与排序相关，所以还可以使用窗口函数进行排序，又因为可能出现并列薪资的关系，所以窗口函数不能采用row()number(),采用rank()和dese()rank()

具体使用详情可参考往期文章

​​SQL排序函数详解+案例实战_Begin to change的博客-​​

将排序之后的表作为子表，然后再跟原表关联查询

select b.name Department, a.salary Salary, a.name Employeefrom (select *, rank() over (PARTITION by departmentId order by salary desc) rn from Employee ) a join Department b on a.departmentId = b.id and a.rn = 1

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。