Leetcode-sql-four

LeetCode-SQL-four

本文中主要是介绍LeetCode中关于SQL的练习题，从易到难，循序渐进。文中会介绍题目和提供尽可能多的解答方案。从本文开始属于$\color{red}{中等}$难度

本文中更多的是关于分组功能的使用

180-连续出现的数字

题目

编写一个SQL 查询，查找所有至少连续出现三次的数字。

答案

先检查是否3个连续相同的数字

select *
from Logs l1, Logs l2, Logs l3
where l1.Id = l2.Id - 1
and l2.Id = l3.Id - 1
and l1.Num = l2.Num
and l2.Num = l3.Num

再使用distinct进行去重

-- 解法1
select distinct l1.Num as consecutiveNums
from Logs l1, Logs l2, Logs l3
where l1.Id = l2.Id - 1
and l2.Id = l3.Id - 1   -- 连续出现3次的写法，可以推广到连续N次
and l1.Num = l2.Num
and l2.Num = l3.Num

-- 解法2
select distinct c.Num as consecutiveNums
from Logs as a
left join Logs as b on a.Id = b.Id+1
left join Logs as c on a.Id = c.Id+2
where a.Num = b.Num
and a.Num=c.Num;

184-部分工资最高的员工

题目

根据下图找出每个部门工资最高的员工

答案

先找出最高的工资

1
2
3

select DepartmentId, max(salary)
from Employee
group by DepartmentId;

将题目中的两个表连接起来，并且把上面的结果作为临时表，进行查询

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)

多个字段可以同时使用in关键字

SELECT Department.NAME AS Department, Employee.NAME AS Employee, Salary
FROM Employee, Department
WHERE Employee.DepartmentId = Department.Id
AND ( Employee.DepartmentId, Salary )
in (select DepartmentId, max(Salary)   -- 多个字段使用in
     from Employee
     group by DepartmentId)

-- 方法3
select d.Name as Department,em.Name as Employee,em.Salary as Salary
    from (
        select Max(Salary) as Salary,DepartmentId
        from Employee
        group by DepartmentId
    ) as e  -- 将查询到的最大薪水和部门作为临时表，和已知的两个表进行连接
    join Employee em
    join Department d
    on d.Id = e.DepartmentId
    and em.Salary = e.Salary
    and em.DepartmentId = e.DepartmentId

如何实现分组问题

现在有下图中的四张表，现在要查找出每门课程中成绩最好的学生的姓名和该学生的课程及成绩。

$\color{red}{需要注意}$：可能出现并列第一的情况

多表查询

涉及到的表有：学生表，成绩表，课程表，需要使用多表查询。多表查询中可能使用到的3种查询方式：

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

本题中使用成绩表作为中间桥梁来进行查询

select c.学号, c.姓名, b.课程, a.成绩  -- 步骤1：实现多表查询
from 成绩表 a
left join 课程表 b on a.课程号=b.课程号
left join 学生表 c on a.学号=c.学号

确定查询条件

确定查询的条件：每门课程使用group by 进行分组，最大值使用max函数

1
2
3

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

将上述两个查询进行合并

select c.学号, c.姓名, b.课程, a.成绩  -- 步骤1：实现多表查询
from 成绩表 a
left join 课程表 b on a.课程号=b.课程号
left join 学生表 c on a.学号=c.学号
where (a.课程号, a.成绩) in (select 课程号,max(成绩)  -- 确定查询条件
                        from 成绩
                        group by 课程号);