【SQL高频练习带刷】day10：补充练习题

题目一：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
    )

题目二：部门工资前三高的所有员工

题目要求：

公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。

编写解决方案，找出每个部门中 收入高的员工 。

以 任意顺序 返回结果表。

表结构：

 运行结果示例：

思路：

        本题的难点在于如何“按照部门分类”找到每个部门前三高的工资。其实很多人都会钻牛角尖，想要找到部门id和对应的前三工资数额，再使用in来匹配结果（没错说的就是我）。但是换一个思路，我们可以找“工资比这些员工高的不到3个”,。这种情况下，处理起来就简单多了，我们只需要使用子查询即可。考虑到效率问题，我们可以用自连接改进一下，我们找到同一个部门中，表1比表2更高的工资数据，再连接表Department，获取对应部门id的部门名称，最后选出count(distinct e2.salary)的结果小于等于3的数据即可。

运行代码示例：

select 
    d.name as department, e1.name as employee, e1.salary as salary
from department d 
join employee e1 on d.id = e1.departmentid
join employee e2 on e1.departmentid = e2.departmentid and e1.salary<=e2.salary
group by 
    d.name, e1.name
having 
    count(distinct e2.salary)<=3

题目三：指定日期的产品价格

题目要求：

编写一个解决方案，找出在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10 。

以 任意顺序 返回结果表。

表结构：

 运行结果示例：

思路：

本题如果直接解决，会比较复杂，整体上我们可以考虑使用子查询或者窗口函数来解决，并不难，细心就好。这里我们提供一个较为简单的思路，使用union连接查询结果。

我们可以看出，题目中存在两种情况，第一种是在2019-08-16这个时间之前有价格数据的，我们以最新数据为准，另一种是在该节点之前没有价格数据的，我们按照初始价格为10来考虑。我们直接分别查询两种情况的结果，使用union连接即可。

对于第一种情况，我们可以分组统计找到每个产品在2019-08-16之前的最后一次修改时间，再去查询对应的价格数据。第二种情况则更为简单，我们只需要找到在2019-08-16之前没有出现在表里的数据。

运行代码示例：

select product_id ,new_price as price
from Products
where (product_id,change_date) in (select product_id,max(change_date) from Products where change_date <= '2019-08-16' group by product_id )
union 
select product_id,10 as price
from Products
where product_id not in (select product_id from Products where change_date <= '2019-08-16')

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

题目要求：

有一队乘客在等着上巴士。然而，巴士有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

题目五：按分类统计薪水

题目要求：

查询每个工资类别的银行账户数量。 工资类别如下：

"Low Salary"：所有工资 严格低于 20000 美元。
"Average Salary"： 包含 范围内的所有工资 [$20000, $50000] 。
"High Salary"：所有工资 严格大于 50000 美元。

结果表 必须 包含所有三个类别。 如果某个类别中没有帐户，则报告 0 。

按 任意顺序 返回结果表。

表结构：

 运行结果示例：

思路：

我们分别按照统计规律筛选出对应的标签和数量，再进行合并结果集即可。

运行代码示例：

select 'Low Salary' as category,ifnull(count(*),0) as accounts_count
from Accounts
where income < 20000
union
select 'Average Salary' as category,ifnull(count(*),0) as accounts_count
from Accounts
where income <= 50000 and income >= 20000
union
select 'High Salary' as category,ifnull(count(*),0) as accounts_count
from Accounts
where income > 50000