【力扣 | SQL题 | 每日四题】力扣613， 579， 578， 580， 585

1easy, 3mid, 1hard，做起来比较顺手都秒了。

1. 力扣613：直线上的最近距离

1.1 题目：

表： Point

+-------------+------+
| Column Name | Type |
+-------------+------+
| x           | int  |
+-------------+------+
在SQL中，x是该表的主键列。
该表的每一行表示X轴上一个点的位置。

找到 Point 表中任意两点之间的最短距离。

返回结果格式如下例所示。

示例 1：

输入：
Point 表:
+----+
| x  |
+----+
| -1 |
| 0  |
| 2  |
+----+
输出：
+----------+
| shortest |
+----------+
| 1        |
+----------+
解释：点 -1 和 0 之间的最短距离为 |(-1) - 0| = 1。

进阶：如果 Point 表按 升序排列，如何优化你的解决方案？

1.2 思路：

题目没有说原表就是有序的，我排了一下序。然后窗口函数给上id值。然后自连接解决。

1.3 题解：

-- 可以将任意两点间的距离转化为求
-- 有序排列的数组，相邻两点间的最小距离
with Poi as (
    select *
    from Point
    order by x
), tep as (
    -- 使用窗口函数，标上id
    select x, row_number() over (order by x) id
    from Poi
), temp as (
    -- 然后计算相邻两点的距离
    select case 
    when t2.x - t1.x > 0 then t2.x - t1.x
    when t2.x - t1.x < 0 then t1.x - t2.x
    else 0
    end y
    from tep t1, tep t2
    where t1.id -1 = t2.id
)
-- 升序排列，第一个即是最小的
select y shortest
from temp
order by y 
limit 0, 1

2. 力扣579：查询员工的累计薪水

2.1 题目：

表：Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| month       | int  |
| salary      | int  |
+-------------+------+
(id, month) 是该表的主键(具有唯一值的列的组合)。
表中的每一行表示 2020 年期间员工一个月的工资。

编写一个解决方案，在一个统一的表中计算出每个员工的 累计工资汇总 。

员工的 累计工资汇总 可以计算如下:

对于该员工工作的每个月，将该月和 前两个月 的工资加起来。这是他们当月的 3 个月总工资和。如果员工在前几个月没有为公司工作，那么他们在前几个月的有效工资为 0 。
不要在摘要中包括员工 最近一个月 的 3 个月总工资和。
不要包括雇员 没有工作 的任何一个月的 3 个月总工资和。

返回按 id 升序排序 的结果表。如果 id 相等，请按 month 降序排序。

结果格式如下所示。

示例 1

输入：
Employee table:
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 1  | 2     | 30     |
| 2  | 2     | 30     |
| 3  | 2     | 40     |
| 1  | 3     | 40     |
| 3  | 3     | 60     |
| 1  | 4     | 60     |
| 3  | 4     | 70     |
| 1  | 7     | 90     |
| 1  | 8     | 90     |
+----+-------+--------+
输出：
+----+-------+--------+
| id | month | Salary |
+----+-------+--------+
| 1  | 7     | 90     |
| 1  | 4     | 130    |
| 1  | 3     | 90     |
| 1  | 2     | 50     |
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 3  | 3     | 100    |
| 3  | 2     | 40     |
+----+-------+--------+
解释：
员工 “1” 有 5 条工资记录，不包括最近一个月的 “8”:
- 第 '7' 个月为 90。
- 第 '4' 个月为 60。
- 第 '3' 个月是 40。
- 第 '2' 个月为 30。
- 第 '1' 个月为 20。
因此，该员工的累计工资汇总为:
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 1  | 7     | 90     |  (90 + 0 + 0)
| 1  | 4     | 130    |  (60 + 40 + 30)
| 1  | 3     | 90     |  (40 + 30 + 20)
| 1  | 2     | 50     |  (30 + 20 + 0)
| 1  | 1     | 20     |  (20 + 0 + 0)
+----+-------+--------+
请注意，'7' 月的 3 个月的总和是 90，因为他们没有在 '6' 月或 '5' 月工作。

员工 '2' 只有一个工资记录('1' 月)，不包括最近的 '2' 月。
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 2  | 1     | 20     |  (20 + 0 + 0)
+----+-------+--------+

员工 '3' 有两个工资记录，不包括最近一个月的 '4' 月:
- 第 '3' 个月为 60 。
- 第 '2' 个月是 40。
因此，该员工的累计工资汇总为:
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 3  | 3     | 100    |  (60 + 40 + 0)
| 3  | 2     | 40     |  (40 + 0 + 0)
+----+-------+--------+

2.2 思路：

看注释，非常简单。

2.3 题解：

-- 满足第二个要求，不要在摘要中包括员工最近一个月的总工资和
with tep as (
    select *
    from Employee e1
    where month <> (
        select max(month)
        from Employee e2
        where e1.id = e2.id
    )
)
,temp as (
    -- ifNull函数是可以解决前一个月/前两个月的工资是否存在的情况。
    -- 我记得还有一个函数nvl也可以做到
    select id, month, sum(Salary) + ifNull((select sum(Salary) from tep t2 where t1.month -1 = t2.month and t1.id = t2.id), 0)
    + ifNull((select sum(Salary) from tep t3 where t1.month -2 = t3.month and t1.id = t3.id), 0) Salary
    from tep t1
    group by id, month
)

-- 最后再以排序字段输出
select *
from temp
order by id, month desc;

3. 力扣578：查询回答率最高的问题

3.1 题目：

SurveyLog 表：

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| action      | ENUM |
| question_id | int  |
| answer_id   | int  |
| q_num       | int  |
| timestamp   | int  |
+-------------+------+
这张表可能包含重复项。
action 是一个 ENUM(category) 数据，可以是 "show"、"answer" 或者 "skip" 。
这张表的每一行表示：ID = id 的用户对 question_id 的问题在 timestamp 时间进行了 action 操作。
如果用户对应的操作是 "answer" ，answer_id 将会是对应答案的 id ，否则，值为 null 。
q_num 是该问题在当前会话中的数字顺序。

回答率 是指：同一问题编号中回答次数占显示次数的比率。

编写一个解决方案以报告 回答率 最高的问题。如果有多个问题具有相同的最大 回答率 ，返回 question_id 最小的那个。

查询结果如下例所示。

示例 1：

输入：
SurveyLog table:
+----+--------+-------------+-----------+-------+-----------+
| id | action | question_id | answer_id | q_num | timestamp |
+----+--------+-------------+-----------+-------+-----------+
| 5  | show   | 285         | null      | 1     | 123       |
| 5  | answer | 285         | 124124    | 1     | 124       |
| 5  | show   | 369         | null      | 2     | 125       |
| 5  | skip   | 369         | null      | 2     | 126       |
+----+--------+-------------+-----------+-------+-----------+
输出：
+------------+
| survey_log |
+------------+
| 285        |
+------------+
解释：
问题 285 显示 1 次、回答 1 次。回答率为 1.0 。
问题 369 显示 1 次、回答 0 次。回答率为 0.0 。
问题 285 回答率最高。

3.2 思路：

分析题意一步一步写出sql语句即可。

3.3 题解：

-- 分析出该题的主要考察字段是action, question_id 
with tep as (
    select action, question_id 
    from SurveyLog
), temp as (
    -- 然后计算每个问题对应的回答率
    select question_id, (select count(*) from tep t1 where t.question_id = t1.question_id and action = 'answer') / (select count(*) from tep t2 where t.question_id = t2.question_id) scala
    from tep t
    group by question_id
)
-- 然后回答率逆序，question_id升序排列
select question_id survey_log
from temp
order by scala desc, question_id
limit 0, 1;

4. 力扣580：统计各专业学生人数

4.1 题目：

表: Student

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| student_name | varchar |
| gender       | varchar |
| dept_id      | int     |
+--------------+---------+
student_id 是该表的主键（具有唯一值的列）。
dept_id是Department表中dept_id的外键。
该表的每一行都表示学生的姓名、性别和所属系的id。

表: Department

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| dept_id     | int     |
| dept_name   | varchar |
+-------------+---------+
dept_id是该表的主键（具有唯一值的列）。
该表的每一行包含一个部门的id和名称。

编写解决方案，为 Department 表中的所有部门(甚至是没有当前学生的部门)报告各自的部门名称和每个部门的学生人数。

按 student_number 降序返回结果表。如果是平局，则按 dept_name 的 字母顺序 排序。

结果格式如下所示。

示例 1:

输入: 
Student 表:
+------------+--------------+--------+---------+
| student_id | student_name | gender | dept_id |
+------------+--------------+--------+---------+
| 1          | Jack         | M      | 1       |
| 2          | Jane         | F      | 1       |
| 3          | Mark         | M      | 2       |
+------------+--------------+--------+---------+
Department 表:
+---------+-------------+
| dept_id | dept_name   |
+---------+-------------+
| 1       | Engineering |
| 2       | Science     |
| 3       | Law         |
+---------+-------------+
输出: 
+-------------+----------------+
| dept_name   | student_number |
+-------------+----------------+
| Engineering | 2              |
| Science     | 1              |
| Law         | 0              |
+-------------+----------------+

4.2 思路：

4.3 题解：

-- 左外连接，保证包括了当前没有学生的部门
with tep as (
    select dept_name, gender, d.dept_id
    from Department d 
    left join Student s 
    on d.dept_id = s.dept_id
), temp as (
    -- 分组，然后查询每个部门的个数
    select dept_name, (select count(*) from tep t1 where t1.dept_id = t.dept_id and gender is not null) student_number
    from tep t
    group by dept_name
)
-- 然后排序输出
select *
from temp
order by student_number desc, dept_name

5. 力扣585：2016年的投资

5.1 题目：

Insurance 表：

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| pid         | int   |
| tiv_2015    | float |
| tiv_2016    | float |
| lat         | float |
| lon         | float |
+-------------+-------+
pid 是这张表的主键(具有唯一值的列)。
表中的每一行都包含一条保险信息，其中：
pid 是投保人的投保编号。
tiv_2015 是该投保人在 2015 年的总投保金额，tiv_2016 是该投保人在 2016 年的总投保金额。
lat 是投保人所在城市的纬度。题目数据确保 lat 不为空。
lon 是投保人所在城市的经度。题目数据确保 lon 不为空。

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

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

tiv_2016 四舍五入的 两位小数 。

查询结果格式如下例所示。

示例 1：

输入：
Insurance 表：
+-----+----------+----------+-----+-----+
| pid | tiv_2015 | tiv_2016 | lat | lon |
+-----+----------+----------+-----+-----+
| 1   | 10       | 5        | 10  | 10  |
| 2   | 20       | 20       | 20  | 20  |
| 3   | 10       | 30       | 20  | 20  |
| 4   | 10       | 40       | 40  | 40  |
+-----+----------+----------+-----+-----+
输出：
+----------+
| tiv_2016 |
+----------+
| 45.00    |
+----------+
解释：
表中的第一条记录和最后一条记录都满足两个条件。
tiv_2015 值为 10 与第三条和第四条记录相同，且其位置是唯一的。

第二条记录不符合任何一个条件。其 tiv_2015 与其他投保人不同，并且位置与第三条记录相同，这也导致了第三条记录不符合题目要求。
因此，结果是第一条记录和最后一条记录的 tiv_2016 之和，即 45 。

5.2 思路：

主要是解决怎么根据lat，lon字段确定一个人的位置。

5.3 题解：

with tep as (
    -- lat*lon-1 lacation在于使得每个人的位置是唯一的（至少在案例中是这样的）
    select pid, tiv_2015, tiv_2016, lat*lon-1 lacation
    from Insurance
)



select round(sum(tiv_2016), 2) tiv_2016
from tep t
-- 先确保tiv_2015至少跟一个其他投保人的tiv_2015一样
where tiv_2015 in (select tiv_2015 from tep t1 where t1.pid <> t.pid)
-- 这个再保证地址跟其他所有投保人都不一样
and lacation not in (select lacation from tep t2 where t2.pid <> t.pid)