sql分组取出每组最大一条_leetcode题库-sql练习精讲系列--八、分组求最大最小类问题...

这是一个系列文章，这个系列的理念是通过一道题，搞懂一类题。涵盖了SQL面试最常考的知识点。搞懂这些题，面试时工作中sql不可能有问题。

文章分为引入问题-完整解析-答案-leetcode题和答案-知识点拓展-BAT等大厂面试真题几个部分。

希望能帮你全方位的弄懂。有问题可以留言，码字不易，写一篇要好几个小时，希望能得到点赞收藏哦

一、问题

这里有一张学生成绩表，其中包含学生编号，课程编号，成绩；还有一张课程表，其中包含课程编号，课程名称。求每门课程编号，课程名称以及每门课程最高的成绩。

​

二、准备

create table 课程(
    课程编号 varchar(10),
    课程名称 nvarchar(10),
    教师编号 varchar(10));

insert into 课程 values('01' , '语文' , '02');
insert into 课程 values('02' , '数学' , '01');
insert into 课程 values('03' , '英语' , '03');

create table `成绩`
(`学生编号` varchar(10),
`课程编号` varchar(10),
`成绩` decimal(18,1));

insert into `成绩` values('01' , '01' , 80);
insert into `成绩` values('01' , '02' , 90);
insert into `成绩` values('01' , '03' , 99);
insert into `成绩` values('02' , '01' , 70);
insert into `成绩` values('02' , '02' , 60);
insert into `成绩` values('02' , '03' , 80);
insert into `成绩` values('03' , '01' , 80);
insert into `成绩` values('03' , '02' , 80);
insert into `成绩` values('03' , '03' , 80);
insert into `成绩` values('04' , '01' , 50);
insert into `成绩` values('04' , '02' , 30);
insert into `成绩` values('04' , '03' , 20);
insert into `成绩` values('05' , '01' , 76);
insert into `成绩` values('05' , '02' , 87);
insert into `成绩` values('06' , '01' , 31);
insert into `成绩` values('06' , '03' , 34);
insert into `成绩` values('07' , '02' , 89);
insert into `成绩` values('07' , '03' , 98);

三、解答

[思路讲解]

1.首先确定题干要求的查询结果

其中课程名称保存在课程表中，成绩保存在成绩表中。两表都有课程编号。所以需要使用多表连接。还需要找出每科最高分，所以需要把课程编号和每科最高分作为关联两表的筛选条件加上。

select 课程.课程编号, 课程.课程名称, 成绩.每科最高分

2.先查找出每门课程的最高分。——确定查询条件

SELECT 课程编号, MAX(成绩)
FROM 成绩
GROUP BY 课程编号;

3.连接两表。确定连接主键和筛选条件。我们在第2步确定了查询条件，所以在这一步加到两表连接的筛选条件中。

select b.课程编号, b.课程名称, a.成绩
from 成绩 a
join 课程 b 
on a.课程编号=b.课程编号
where (a.课程编号,a.成绩) in
(select 课程编号,max(成绩) from 成绩 group by 课程编号);

4.考虑下有可能出现最高分重复的情况。所以在课程编号前加上去重函数

[代码]

select DISTINCT b.课程编号, b.课程名称, a.成绩
from 成绩 a
join 课程 b 
on a.课程编号=b.课程编号
where (a.课程编号,a.成绩) in
(select 课程编号,max(成绩) from 成绩 group by 课程编号);

[本题考点]

1.考察多表的连接。我们可以查看前面写过的多表连接的文。如果有多表连接问题，看下图就好。

2.考察子查询，且使用多列作为筛选条件

[本题答案]

[leetcode题库问题-184. 部门工资最高的员工 ]

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

Department 表包含公司所有部门的信息。

编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

[参考答案]

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
    )