【力扣 | SQL题 | 每日四题】力扣1581, 1811, 1821, 1831

今天的题目就1811这个比较难，其他非常的基础。

1. 力扣1581：进店却未进行过交易的顾客

1.1 题目：

表：Visits

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| visit_id    | int     |
| customer_id | int     |
+-------------+---------+
visit_id 是该表中具有唯一值的列。
该表包含有关光临过购物中心的顾客的信息。

表：Transactions

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| transaction_id | int     |
| visit_id       | int     |
| amount         | int     |
+----------------+---------+
transaction_id 是该表中具有唯一值的列。
此表包含 visit_id 期间进行的交易的信息。

有一些顾客可能光顾了购物中心但没有进行交易。请你编写一个解决方案，来查找这些顾客的 ID ，以及他们只光顾不交易的次数。

返回以 任何顺序 排序的结果表。

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

示例 1：

输入:
Visits
+----------+-------------+
| visit_id | customer_id |
+----------+-------------+
| 1        | 23          |
| 2        | 9           |
| 4        | 30          |
| 5        | 54          |
| 6        | 96          |
| 7        | 54          |
| 8        | 54          |
+----------+-------------+
Transactions
+----------------+----------+--------+
| transaction_id | visit_id | amount |
+----------------+----------+--------+
| 2              | 5        | 310    |
| 3              | 5        | 300    |
| 9              | 5        | 200    |
| 12             | 1        | 910    |
| 13             | 2        | 970    |
+----------------+----------+--------+
输出:
+-------------+----------------+
| customer_id | count_no_trans |
+-------------+----------------+
| 54          | 2              |
| 30          | 1              |
| 96          | 1              |
+-------------+----------------+
解释:
ID = 23 的顾客曾经逛过一次购物中心，并在 ID = 12 的访问期间进行了一笔交易。
ID = 9 的顾客曾经逛过一次购物中心，并在 ID = 13 的访问期间进行了一笔交易。
ID = 30 的顾客曾经去过购物中心，并且没有进行任何交易。
ID = 54 的顾客三度造访了购物中心。在 2 次访问中，他们没有进行任何交易，在 1 次访问中，他们进行了 3 次交易。
ID = 96 的顾客曾经去过购物中心，并且没有进行任何交易。
如我们所见，ID 为 30 和 96 的顾客一次没有进行任何交易就去了购物中心。顾客 54 也两次访问了购物中心并且没有进行任何交易。

1.2 思路

看注释。

1.3 题解：

select customer_id , count(*) count_no_trans
from Visits
where visit_id not in (-- 查出来交易过的visit_idselect distinct visit_id from Transactions
)
group by customer_id

2. 力扣1811：寻找面试候选人

2.1 题目：

表: Contests

+--------------+------+
| Column Name  | Type |
+--------------+------+
| contest_id   | int  |
| gold_medal   | int  |
| silver_medal | int  |
| bronze_medal | int  |
+--------------+------+
contest_id 是该表的主键.
该表包含LeetCode竞赛的ID和该场比赛中金牌、银牌、铜牌的用户id。
可以保证，所有连续的比赛都有连续的ID，没有ID被跳过。

Table: Users

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| mail        | varchar |
| name        | varchar |
+-------------+---------+
user_id 是该表的主键.
该表包含用户信息。

编写 SQL 语句来返回 所有面试候选人 的姓名 name 和邮件 mail 。当用户满足以下两个要求中的 任意一条 ，其成为 面试候选人 :

• 该用户在 连续三场及更多 比赛中赢得 任意 奖牌。
• 该用户在 三场及更多不同的 比赛中赢得 金牌（这些比赛可以不是连续的）

可以以 任何顺序 返回结果。

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

示例 1：

输入：
Contests表:
+------------+------------+--------------+--------------+
| contest_id | gold_medal | silver_medal | bronze_medal |
+------------+------------+--------------+--------------+
| 190        | 1          | 5            | 2            |
| 191        | 2          | 3            | 5            |
| 192        | 5          | 2            | 3            |
| 193        | 1          | 3            | 5            |
| 194        | 4          | 5            | 2            |
| 195        | 4          | 2            | 1            |
| 196        | 1          | 5            | 2            |
+------------+------------+--------------+--------------+
Users表:
+---------+--------------------+-------+
| user_id | mail               | name  |
+---------+--------------------+-------+
| 1       | [e-mail] | Sarah |
| 2       | [e-mail]   | Bob   |
| 3       | [e-mail] | Alice |
| 4       | [e-mail] | Hercy |
| 5       | [e-mail] | Quarz |
+---------+--------------------+-------+
输出：
+-------+--------------------+
| name  | mail               |
+-------+--------------------+
| Sarah | [e-mail] |
| Bob   | [e-mail]   |
| Alice | [e-mail] |
| Quarz | [e-mail] |
+-------+--------------------+
解释：
Sarah 赢得了3块金牌 (190, 193, and 196),所以我们将她列入结果表。
Bob在连续3场竞赛中赢得了奖牌(190, 191, and 192), 所以我们将他列入结果表。- 注意他在另外的连续3场竞赛中也赢得了奖牌(194, 195, and 196).
Alice在连续3场竞赛中赢得了奖牌 (191, 192, and 193), 所以我们将她列入结果表。
Quarz在连续5场竞赛中赢得了奖牌(190, 191, 192, 193, and 194), 所以我们将他列入结果表。

进阶：

• 如果第一个条件变成“该用户在连续 n 场及比赛中赢得任意奖牌。”呢？你如何更改你的解法，来选出面试候选人？可以把 n 想象成存储过程中的参数。
• 有的用户可能没有参加每一场竞赛，但是在参加的每一场竞赛中都表现得不错。你如何更改你的解法，以达到只考虑那些 用户参与了的 比赛？可假设另一张表给出了每场比赛的注册用户信息。

2.2 思路：

先找到获得3个以上金牌的人

再找到连续三天获得奖牌的人,再这之前需要用到临时表炸裂.

然后再union(会去重)

2.3 题解：

-- 将Contests表炸开
with tep1 as (select contest_id , gold_medal medal from Contestsunion all select contest_id , silver_medal medal from Contestsunion all select contest_id , bronze_medal medal from Contests
)select name, mail
from Users
-- 此处的select语句找到连续三天获得就奖牌的man
-- from tep1 t1, tep1 t2, tep1 t3为笛卡尔积
join (select distinct t1.medal medal
from tep1 t1, tep1 t2, tep1 t3
where t1.contest_id + 1 = t2.contest_id and t1.medal = t2.medal
and t2.contest_id + 1 = t3.contest_id and t2.medal = t3.medal) t 
on t.medal = user_id -- union去重，union all不去重
union 
-- 查询到了三个或以上金牌的man
select name, mail
from (select gold_medalfrom Contestsgroup by gold_medalhaving count(*) >= 3) tep
join Users
on tep.gold_medal = user_id

3. 力扣1821：寻找今年具有正收入的客户

3.1 题目：

表：Customers

+--------------+------+
| Column Name  | Type |
+--------------+------+
| customer_id  | int  |
| year         | int  |
| revenue      | int  |
+--------------+------+
(customer_id, year) 是该表的主键（具有唯一值的列的组合）。
这个表包含客户 ID 和不同年份的客户收入。
注意，这个收入可能是负数。

编写一个解决方案来报告 2021 年具有 正收入 的客户。

可以以 任意顺序 返回结果表。

结果格式如下示例所示。

示例 1:

Input:
Customers
+-------------+------+---------+
| customer_id | year | revenue |
+-------------+------+---------+
| 1           | 2018 | 50      |
| 1           | 2021 | 30      |
| 1           | 2020 | 70      |
| 2           | 2021 | -50     |
| 3           | 2018 | 10      |
| 3           | 2016 | 50      |
| 4           | 2021 | 20      |
+-------------+------+---------+Output:
+-------------+
| customer_id |
+-------------+
| 1           |
| 4           |
+-------------+
客户 1 在 2021 年的收入等于 30 。
客户 2 在 2021 年的收入等于 -50 。
客户 3 在 2021 年没有收入。
客户 4 在 2021 年的收入等于 20 。
因此，只有客户 1 和 4 在 2021 年有正收入。

3.2 思路：

简单题。

3.3 题解：

with tep as (select *from Customerswhere year = '2021'
)select customer_id
from tep
where revenue > 0

4. 1831：每天的最大交易

4.1 题目：

表：Transactions

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| transaction_id | int      |
| day            | datetime |
| amount         | int      |
+----------------+----------+
transaction_id 是该表具有唯一值的列。
每行包括了该次交易的信息。

编写一个解决方案，报告每天交易金额 amount 最大 的交易 ID 。如果一天中有多个这样的交易，返回这些交易的 ID 。

返回结果根据 transaction_id 升序排列。

返回格式如下示例所示：

示例 1：

输入：
Transactions table:
+----------------+--------------------+--------+
| transaction_id | day                | amount |
+----------------+--------------------+--------+
| 8              | 2021-4-3 15:57:28  | 57     |
| 9              | 2021-4-28 08:47:25 | 21     |
| 1              | 2021-4-29 13:28:30 | 58     |
| 5              | 2021-4-28 16:39:59 | 40     |
| 6              | 2021-4-29 23:39:28 | 58     |
+----------------+--------------------+--------+
输出：
+----------------+
| transaction_id |
+----------------+
| 1              |
| 5              |
| 6              |
| 8              |
+----------------+
解释：
"2021-4-3"  --> 有一个 id 是 8 的交易，因此，把它加入结果表。 
"2021-4-28" --> 有两个交易，id 是 5 和 9 ，交易 5 的金额是 40 ，而交易 9 的数量是 21 。只需要将交易 5 加入结果表，因为它是当天金额最大的交易。
"2021-4-29" --> 有两个交易，id 是 1 和 6 ，这两个交易的金额都是 58 ，因此需要把它们都写入结果表。
最后，把交易 id 按照升序排列。

进阶：你可以不使用 MAX() 函数解决这道题目吗?

4.2 思路：

使用datediff函数找到同一天的日期，然后判断是不是该天的最大交易金额。

4.3 题解 ：

select transaction_id
from Transactions t1
-- 先过滤掉每天不是最大金额的交易ID
where amount = (select max(amount) from Transactions t2 where datediff(t1.day, t2.day) = 0)
-- 然后再排序
order by transaction_id