【力扣 | SQL题 | 每日5题】力扣2362, 2356, 2394, 2480, 2388

1. 力扣2362：生成发票

1.1 题目：

表: Products

+-------------+------+
| Column Name | Type |
+-------------+------+
| product_id  | int  |
| price       | int  |
+-------------+------+
product_id 包含唯一值。
该表中的每一行显示了一个产品的 ID 和一个单位的价格。

表: Purchases

+-------------+------+
| Column Name | Type |
+-------------+------+
| invoice_id  | int  |
| product_id  | int  |
| quantity    | int  |
+-------------+------+
(invoice_id, product_id) 是该表的主键（具有唯一值的列的组合）
该表中的每一行都显示了从发票中的一种产品订购的数量。

编写解决方案，展示价格最高的发票的详细信息。如果两个或多个发票具有相同的价格，则返回 invoice_id 最小的发票的详细信息。

以 任意顺序 返回结果表。

结果格式示例如下。

示例 1:

输入: 
Products 表:
+------------+-------+
| product_id | price |
+------------+-------+
| 1          | 100   |
| 2          | 200   |
+------------+-------+
Purchases 表:
+------------+------------+----------+
| invoice_id | product_id | quantity |
+------------+------------+----------+
| 1          | 1          | 2        |
| 3          | 2          | 1        |
| 2          | 2          | 3        |
| 2          | 1          | 4        |
| 4          | 1          | 10       |
+------------+------------+----------+
输出: 
+------------+----------+-------+
| product_id | quantity | price |
+------------+----------+-------+
| 2          | 3        | 600   |
| 1          | 4        | 400   |
+------------+----------+-------+
解释: 
发票 1: price = (2 * 100) = $200
发票 2: price = (4 * 100) + (3 * 200) = $1000
发票 3: price = (1 * 200) = $200
发票 4: price = (10 * 100) = $1000最高价格是 1000 美元，最高价格的发票是 2 和 4。我们返回 ID 最小的发票 2 的详细信息。

1.2 思路：

价格最高=>排名第一=>窗口函数=>rank=>ranks=1

1.3 题解：

-- 以invoice_id分组，查询发票的总价格
with tep1 as (select invoice_id , sum(quantity*price) pricefrom Products t1 join Purchases t2 on t1.product_id = t2.product_id group by invoice_id
), tep2 as (-- 依据价格给每个发票一个排名select invoice_id, price, rank() over (order by price desc, invoice_id) ranksfrom tep1
), tep3 as (-- 然后在Purchases表中找到最高价格发票的记录select product_id , quantity from Purchases where invoice_id = (select invoice_id from tep2 where ranks = 1)
)
-- 内连接收尾
select t1.product_id, quantity , quantity*price price 
from tep3 t1
join Products t2 
on t1.product_id = t2.product_id 

2. 力扣2356：每位教师所教授的科目种类的数量

2.1 题目：

表: Teacher

+-------------+------+
| Column Name | Type |
+-------------+------+
| teacher_id  | int  |
| subject_id  | int  |
| dept_id     | int  |
+-------------+------+
在 SQL 中，(subject_id, dept_id) 是该表的主键。
该表中的每一行都表示带有 teacher_id 的教师在系 dept_id 中教授科目 subject_id。

查询每位老师在大学里教授的科目种类的数量。

以 任意顺序 返回结果表。

查询结果格式示例如下。

示例 1:

输入: 
Teacher 表:
+------------+------------+---------+
| teacher_id | subject_id | dept_id |
+------------+------------+---------+
| 1          | 2          | 3       |
| 1          | 2          | 4       |
| 1          | 3          | 3       |
| 2          | 1          | 1       |
| 2          | 2          | 1       |
| 2          | 3          | 1       |
| 2          | 4          | 1       |
+------------+------------+---------+
输出:  
+------------+-----+
| teacher_id | cnt |
+------------+-----+
| 1          | 2   |
| 2          | 4   |
+------------+-----+
解释: 
教师 1:- 他在 3、4 系教科目 2。- 他在 3 系教科目 3。
教师 2:- 他在 1 系教科目 1。- 他在 1 系教科目 2。- 他在 1 系教科目 3。- 他在 1 系教科目 4。

2.2 思路：

简单题。

2.3 题解：

-- 先去重后查询
with tep as (select distinct teacher_id , subject_id from Teacher
)select teacher_id, count(*) cnt 
from tep
group by teacher_id

3. 力扣2394：开除员工

3.1 题目：

表: Employees

+--------------+------+
| Column Name  | Type |
+--------------+------+
| employee_id  | int  |
| needed_hours | int  |
+--------------+------+
employee_id 是该表具有的唯一值的列。
每一行都包含员工的 id 和他们获得工资所需的最低工作时数。

表: Logs

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| employee_id | int      |
| in_time     | datetime |
| out_time    | datetime |
+-------------+----------+
(employee_id, in_time, out_time) 是该表的主键（具有唯一值的列的组合）。
该表的每一行都显示了员工的时间戳。in_time 是员工开始工作的时间，out_time 是员工结束工作的时间。
所有时间都在 2022 年 10 月。out_time 可以是 in_time 之后的一天，这意味着该员工在午夜之后工作。

在公司里，每个员工每个月必须工作一定的小时数。员工在工作段中工作。员工工作的小时数可以通过员工在所有工作段中工作的分钟数的总和来计算。每个工作段的分钟数是向上取整的。

• 例如，如果员工在一个时间段中工作了 51 分 2 秒，我们就认为它是 52 分钟。

编写解决方案来报告将被开除的员工的 id。换句话说，报告没有工作所需时间的员工的 id。

以 任意顺序 返回结果表。

结果格式如下所示。

示例 1:

输入: 
Employees 表:
+-------------+--------------+
| employee_id | needed_hours |
+-------------+--------------+
| 1           | 20           |
| 2           | 12           |
| 3           | 2            |
+-------------+--------------+
Logs 表:
+-------------+---------------------+---------------------+
| employee_id | in_time             | out_time            |
+-------------+---------------------+---------------------+
| 1           | 2022-10-01 09:00:00 | 2022-10-01 17:00:00 |
| 1           | 2022-10-06 09:05:04 | 2022-10-06 17:09:03 |
| 1           | 2022-10-12 23:00:00 | 2022-10-13 03:00:01 |
| 2           | 2022-10-29 12:00:00 | 2022-10-29 23:58:58 |
+-------------+---------------------+---------------------+
输出: 
+-------------+
| employee_id |
+-------------+
| 2           |
| 3           |
+-------------+
解释: 
员工 1:- 参加了三个工作段:- 在 2022-10-01, 他工作了 8 个小时。- 在 2022-10-06, 他工作了 8 小时 4 分钟。- 在 2022-10-12, 他工作了 4 小时 1 分钟。请注意，他一直工作到午夜。- 员工 1 在各个时段总共工作了 20 小时5分钟，不被开除。
员工 2:- 参加了一个工作段:- 在 2022-10-29, 他工作了 11 小时 59 分钟。- 员工 2 没有工作足够的时长，将被开除。
员工 3:- 没有任何工作段。- 员工 3 没有工作足够的时长，将被开除。

3.2 思路：

使用到了timestampdiff函数和ceil函数。

3.3 题解：

-- 使用了timestampdiff函数，第一个参数限定返回值是两个时间段之间的秒数
-- /60再使用ceil天花板函数，=>每个工作段的分钟数是向上取整的
-- 然后分组求每个时间段的和with tep as (select employee_id , sum(ceil(timestampdiff(second, in_time, out_time) / 60) / 60) sum_minfrom Logsgroup by employee_id 
)
-- 这里就是简单的判断比较
select t1.employee_id
from Employees t1 
left join tep t2 
on t1.employee_id = t2.employee_id
where sum_min is null or 
sum_min < needed_hours

4. 力扣2480：形成化学键

4.1 题目：

表: Elements

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| symbol      | varchar |
| type        | enum    |
| electrons   | int     |
+-------------+---------+
symbol 是该表的主键(具有唯一值的列)。
该表的每一行包含一个元素的信息。
type 是 ENUM 类型，它的值是 ('Metal', 'Nonmetal', 'Noble') 之一- 如果 type 是 Noble, electrons 是 0。- 如果 type 是 Metal, electrons 是这种元素的一个原子所能给出的电子数。- 如果 type 是 Nonmetal, electrons 这种元素的一个原子所需要的电子数。

如果一个元素是 'Metal'，另外一个元素是 'Nonmetal' ，那么它们可以形成键。

编写一个解决方案找出所有可以形成键的元素对。

以 任意顺序 返回结果表。

查询结果格式如下所示。

示例 1:

输入: 
Elements 表:
+--------+----------+-----------+
| symbol | type     | electrons |
+--------+----------+-----------+
| He     | Noble    | 0         |
| Na     | Metal    | 1         |
| Ca     | Metal    | 2         |
| La     | Metal    | 3         |
| Cl     | Nonmetal | 1         |
| O      | Nonmetal | 2         |
| N      | Nonmetal | 3         |
+--------+----------+-----------+
输出: 
+-------+----------+
| metal | nonmetal |
+-------+----------+
| La    | Cl       |
| Ca    | Cl       |
| Na    | Cl       |
| La    | O        |
| Ca    | O        |
| Na    | O        |
| La    | N        |
| Ca    | N        |
| Na    | N        |
+-------+----------+
解释: 
Metal 元素包括 La, Ca, and Na.
Nonmetal 元素包括 Cl, O, and N.
每个 Metal 元素与输出表中的 Nonmeal 元素配对。

4.2 思路：

其实本质就是笛卡尔积。

看输出表即可。

4.3 题解：

with tep1 as (select symbol metal from Elements where type = 'Metal'
), tep2 as (select symbol nonmetalfrom Elements where type = 'Nonmetal'
)
select metal, nonmetal
from tep1, tep2

5. 力扣2388：将表中的空值更改为前一个值

5.1 题目：

表: CoffeeShop

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| drink       | varchar |
+-------------+---------+
id 是该表的主键（具有唯一值的列）。
该表中的每一行都显示了订单 id 和所点饮料的名称。一些饮料行为 null。

编写一个解决方案将 drink 的 null 值替换为前面最近一行不为 null 的 drink。保证表第一行的 drink 不为 null。

返回 与输入顺序相同的 结果表。

查询结果格式示例如下。

示例 1:

输入: 
CoffeeShop 表:
+----+-------------------+
| id | drink             |
+----+-------------------+
| 9  | Rum and Coke      |
| 6  | null              |
| 7  | null              |
| 3  | St Germain Spritz |
| 1  | Orange Margarita  |
| 2  | null              |
+----+-------------------+
输出: 
+----+-------------------+
| id | drink             |
+----+-------------------+
| 9  | Rum and Coke      |
| 6  | Rum and Coke      |
| 7  | Rum and Coke      |
| 3  | St Germain Spritz |
| 1  | Orange Margarita  |
| 2  | Orange Margarita  |
+----+-------------------+
解释: 
对于 ID 6，之前不为空的值来自 ID 9。我们将 null 替换为 "Rum and Coke"。
对于 ID 7，之前不为空的值来自 ID 9。我们将 null 替换为 "Rum and Coke"。
对于 ID 2，之前不为空的值来自 ID 1。我们将 null 替换为 "Orange Margarita"。
请注意，输出中的行与输入中的行相同。

5.2 思路：

先给每个记录一个排名，然后对于每条记录来说，如果该记录的drink值为null（不为null的情况没啥讨论的），就自连接查询，where限制排名要低于该记录，并且drink不为null，得到最高的排名。从而得到最高排名的记录。

5.3 题解：

-- 先给原表的每行记录一个排名
with tep as (select id , drink, row_number() over () ranksfrom CoffeeShop
)
-- 然后case when决定drink值
-- 如果drink为null，则需要根据排名找到对应的记录。
-- 首先它的排名应该低于t1表的id，并且drink不为null
-- 然后最大排名的人的drink就是这个没有drink值的人的drink
select id, 
case when drink is not null then drink
else (select drink from tep t2 whereranks = (select max(ranks) from tep where ranks < t1.ranks anddrink is not null)
)
end drink
from tep t1