【SQL每日一练】day5：leetcode高频习题练习

题目一：部门工资最高的员工

题目要求：

查找出每个部门中薪资最高的员工。
按 任意顺序 返回结果表。

表结构：

运行结果示例：

思路：

        本题要求寻找每个部门中工资最高的员工，不难想到要用group by 子句实现，但是根据示例可以看出，结果中存在多者并列的情况，因此不能使用limit子句来筛选。这时我们可以考虑使用子查询来解决这个问题。

        首先我们使用group by子句结合mix()函数，查询出每个部门的最高工资数额，之后我们将得到的结果作为子查询的条件，查询主表中工资数、部门在查询结果中的数据，并连接Department表获取部门名称。

运行代码示例：

select d.name as Department, e.name as Employee,salary
from Employee e
left join Department d 
on e.departmentId = d.id 
where (salary,departmentId) in (
select max(salary),departmentId
from Employee e
group by departmentId) 

题目二：至少有5名直接下属的经理

题目要求：

编写一个解决方案，找出至少有五个直接下属的经理。

以 任意顺序 返回结果表。

表结构：

运行结果示例：

思路：

        本题只有一张数据表，难度也不大。题目要求寻找至少有5名直接下属的经理，观察表结构，可以发现我们需要寻找“id的数量大于等于5的managerId”。需要注意，我们的分组条件是managerId而不是id。同样，虽然样例中输出的结果只有一个，但是理论上我们找到的managerId结果集不仅仅是一个，因此我们要使用in子句来找到所有id = managerId的经理名称。

运行代码示例：

select name from Employee where id in (
    select managerId from Employee 
    group by managerId
    having count(id) >= 5
)

题目三：2016年的投资

题目要求：

编写解决方案报告 2016 年 (tiv_2016) 所有满足下述条件的投保人的投保金额之和：

• 他在 2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
• 他所在的城市必须与其他投保人都不同（也就是说 (lat, lon) 不能跟其他任何一个投保人完全相同）。

tiv_2016 四舍五入的 两位小数 。

表结构：

运行结果示例：

思路：

        这道题题目描述一大串，我们首先先来分解一下题目。题目要求我们找到投保人的投保金额之和，也就是用sum（）函数实现，同时要求tiv_2016保留四舍五入的两位小数，那么我们很容易就能写出select语句中的内容select round(sum(tiv_2016),2) as tiv_2016。

        接下来我们处理筛选条件部分。题目要求我们所使用的数据必须满足两个条件：

        1)在 2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同

        2)所在的城市必须与其他投保人都不同（也就是说 (lat, lon) 不能跟其他任何一个投保人完全相同）。

        对于这两个条件，我们考虑用in关键字结合子查询实现。首先要求2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同，那么我们只需要找到2015年投保额相同的额度是多少即可，也就是按照保额分组，统计数量，筛选出数量大于等于2的数据，并判断主查询中的tiv_2015是否在我们查询中的结果集中。对与所在城市的判定与投保额相同，我们按照经纬度分组，找到只有一条数据的组即可。

运行代码示例：

select round(sum(tiv_2016),2) as tiv_2016
from Insurance
where tiv_2015 in (
    select tiv_2015 from Insurance group by tiv_2015 having count(*) > 1
    ) 
and (lat,lon) in (
    select lat,lon from Insurance group by lat,lon having count(*)=1
    )

题目四：树节点

题目要求：

树中的每个节点可以是以下三种类型之一：

• "Leaf"：节点是叶子节点。
• "Root"：节点是树的根节点。
• "lnner"：节点既不是叶子节点也不是根节点。

编写一个解决方案来报告树中每个节点的类型。

以 任意顺序 返回结果表。

表结构：

运行结果示例：

思路：

        这道题挺有意思的，乍一看似乎无从下手，但其实我们仔细分析，题目中无外乎三种情况：根节点、内部节点、叶子节点，我们直接用判断语句判断就可以了。

        我们参照示例来看，首先根节点有且仅有一个，其特点是p_id为null。而叶子节点和内部节点的区别就在于它们的id有没有出现在p_id列中，如果没有出现，就是叶子节点。这一步我们用in关键字判断即可。

运行代码示例：

select id, 
    (case when p_id is null then 'Root'
        when id in (select p_id from Tree) then 'Inner'
        else 'Leaf' end
    ) as  type
from Tree

题目五：股票的资本损益

题目要求：

编写解决方案报告每只股票的 资本损益。

股票的 资本利得/损失 是指一次或多次买卖该股票后的总收益或损失。

以 任意顺序 返回结果表。

表结构：

运行结果示例：

思路：

        这道题目看似复杂，实际上只考察了我们对于sum（）函数和判断语句的联合使用，如果之前对此有过了解那么这道题目可以说是秒解。我们只需要按照股票名称分组统计价格之和，如果操作为‘buy’那么就是减去价格，否则则加上价格。

运行代码示例：

select stock_name,sum(if(operation = 'Buy',-price,price)) as capital_gain_loss
from Stocks
group by stock_name

题目六：最后一个能进入巴士的人

题目要求：

有一队乘客在等着上巴士。然而，巴士有1000  千克 的重量限制，所以其中一部分乘客可能无法上巴士。

编写解决方案找出 最后一个 上巴士且不超过重量限制的乘客，并报告 person_name 。题目测试用例确保顺位第一的人可以上巴士且不会超重。

表结构：

运行结果示例：

思路：

        本题看似麻烦，实际上考察了窗口函数的使用，窗口函数也是SQL中常见的考点，但是很多学校的课程不教授这部分，大家可以参考【SQL】一张学生表带你学会开窗函数这篇博客中的讲解来学习。

        本题的大致思路就是根据顺序（turn）开窗计算重量之和，之后筛选出所有重量和低于1000千克的乘客，也就是所有能够上车的乘客。在这些乘客中，我们按照排队顺序排序，找到最后一个即可。

运行代码示例：

select person_name
from(
    select turn,person_name,sum(weight) over(order by turn) as cumu_weight from Queue
) t
where cumu_weight <= 1000
order by turn desc
limit 1