Leetcode sql题目记录2

本博客仅记录高频 SQL 50 题（进阶版）中的中等难度题目，后续不定期更新

文章目录

1398.购买了产品A和产品B却没有购买产品C的顾客

Customers 表：

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| customer_id         | int     |
| customer_name       | varchar |
+---------------------+---------+
customer_id 是这张表中具有唯一值的列。
customer_name 是顾客的名称。

Orders 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| customer_id   | int     |
| product_name  | varchar |
+---------------+---------+
order_id 是这张表中具有唯一值的列。
customer_id 是购买了名为 "product_name" 产品顾客的id。

请你编写解决方案，报告购买了产品 “A”，“B” 但没有购买产品 “C” 的客户的 customer_id 和 customer_name，因为我们想推荐他们购买这样的产品。

返回按 customer_id 排序 的结果表。

返回结果格式如下所示。

示例 1：

输入：
Customers table:
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1           | Daniel        |
| 2           | Diana         |
| 3           | Elizabeth     |
| 4           | Jhon          |
+-------------+---------------+

Orders table:
+------------+--------------+---------------+
| order_id   | customer_id  | product_name  |
+------------+--------------+---------------+
| 10         |     1        |     A         |
| 20         |     1        |     B         |
| 30         |     1        |     D         |
| 40         |     1        |     C         |
| 50         |     2        |     A         |
| 60         |     3        |     A         |
| 70         |     3        |     B         |
| 80         |     3        |     D         |
| 90         |     4        |     C         |
+------------+--------------+---------------+
输出：
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 3           | Elizabeth     |
+-------------+---------------+
解释：
只有 customer_id 为 3 的顾客购买了产品 A 和产品 B ，却没有购买产品 C 。

解答：

（1）最基础解法，逐一子查询

SELECT c.customer_id, c.customer_name
FROM Customers c
WHERE EXISTS (
    SELECT 1 FROM Orders o WHERE o.customer_id = c.customer_id AND o.product_name = 'A'
)
AND EXISTS (
    SELECT 1 FROM Orders o WHERE o.customer_id = c.customer_id AND o.product_name = 'B'
)
AND NOT EXISTS (
    SELECT 1 FROM Orders o WHERE o.customer_id = c.customer_id AND o.product_name = 'C'
);

注意：在判断某列不在某个子查询的结果列表里时，如果子查询里有null值，则not in会返回空。因此需要用not exists。

当 NOT IN 拆成多个比较后，如果其中有一个条件返回 UNKNOWN，整个 AND 结果就不是 TRUE，而是 UNKNOWN，最终这条记录就被过滤掉。

举例：

  • col1 = 10
    检查 10 <> 5 → TRUE
    检查 10 <> NULL → UNKNOWN
    TRUE AND UNKNOWN → UNKNOWN → 不返回

所以只要子查询里有 NULL，所有外层行都会因为遇到 UNKNOWN 被排除掉，结果就是空集

（2）一次扫 Orders 做条件聚合，再跟 Customers 关联

SELECT c.customer_id, c.customer_name
FROM Customers c
JOIN Orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING SUM(o.product_name = 'A') > 0
   AND SUM(o.product_name = 'B') > 0
   AND SUM(o.product_name = 'C') = 0
ORDER BY c.customer_id;

1112.每位学生的最高成绩

表：Enrollments

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| course_id     | int     |
| grade         | int     |
+---------------+---------+
(student_id, course_id) 是该表的主键（具有唯一值的列的组合）。
grade 不会为 NULL。

编写解决方案，找出每位学生获得的最高成绩和它所对应的科目，若科目成绩并列，取 course_id 最小的一门。查询结果需按 student_id 增序进行排序。

查询结果格式如下所示。

示例 1：

输入：
Enrollments 表：
+------------+-------------------+
| student_id | course_id | grade |
+------------+-----------+-------+
| 2          | 2         | 95    |
| 2          | 3         | 95    |
| 1          | 1         | 90    |
| 1          | 2         | 99    |
| 3          | 1         | 80    |
| 3          | 2         | 75    |
| 3          | 3         | 82    |
+------------+-----------+-------+
输出：
+------------+-------------------+
| student_id | course_id | grade |
+------------+-----------+-------+
| 1          | 2         | 99    |
| 2          | 2         | 95    |
| 3          | 3         | 82    |
+------------+-----------+-------+

解答：

select student_id, min(course_id)as course_id, grade from(
    select *, max(grade) over(partition by student_id) as max_score from Enrollments
)t where grade = max_score group by student_id, grade order by student_id;

1440.计算布尔表达式的值

表 Variables:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| name          | varchar |
| value         | int     |
+---------------+---------+
在 SQL 中，name 是该表主键.
该表包含了存储的变量及其对应的值.

表 Expressions:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| left_operand  | varchar |
| operator      | enum    |
| right_operand | varchar |
+---------------+---------+
在 SQL 中，(left_operand, operator, right_operand) 是该表主键.
该表包含了需要计算的布尔表达式.
operator 是枚举类型, 取值于('<', '>', '=')
left_operand 和 right_operand 的值保证存在于 Variables 表单中.

计算表 Expressions 中的布尔表达式。

返回的结果表 无顺序要求 。

结果格式如下例所示。

示例 1：

输入：
Variables 表:
+------+-------+
| name | value |
+------+-------+
| x    | 66    |
| y    | 77    |
+------+-------+

Expressions 表:
+--------------+----------+---------------+
| left_operand | operator | right_operand |
+--------------+----------+---------------+
| x            | >        | y             |
| x            | <        | y             |
| x            | =        | y             |
| y            | >        | x             |
| y            | <        | x             |
| x            | =        | x             |
+--------------+----------+---------------+

输出:
+--------------+----------+---------------+-------+
| left_operand | operator | right_operand | value |
+--------------+----------+---------------+-------+
| x            | >        | y             | false |
| x            | <        | y             | true  |
| x            | =        | y             | false |
| y            | >        | x             | true  |
| y            | <        | x             | false |
| x            | =        | x             | true  |
+--------------+----------+---------------+-------+
解释：
如上所示, 你需要通过使用 Variables 表来找到 Expressions 表中的每一个布尔表达式的值.

解答：

SELECT e.left_operand,
       e.operator,
       e.right_operand,
       CASE
           WHEN e.operator = '>' AND v1.value > v2.value THEN 'true'
           WHEN e.operator = '<' AND v1.value < v2.value THEN 'true'
           WHEN e.operator = '=' AND v1.value = v2.value THEN 'true'
           ELSE 'false'
       END AS value
FROM Expressions e
JOIN Variables v1 ON e.left_operand = v1.name
JOIN Variables v2 ON e.right_operand = v2.name;

思路

  • JOIN Variables v1 → 获取左操作数的值。
  • JOIN Variables v2 → 获取右操作数的值。
  • CASE 里根据不同运算符进行比较，返回 true 或 false。

这样写就能正确对比 数值，而不是对比变量名字符串。

1212.查询球队积分

表: Teams

+---------------+----------+
| Column Name   | Type     |
+---------------+----------+
| team_id       | int      |
| team_name     | varchar  |
+---------------+----------+
team_id 是该表具有唯一值的列。
表中的每一行都代表一支独立足球队。

表: Matches

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| match_id      | int     |
| host_team     | int     |
| guest_team    | int     | 
| host_goals    | int     |
| guest_goals   | int     |
+---------------+---------+
match_id 是该表具有唯一值的列。
表中的每一行都代表一场已结束的比赛。
比赛的主客队分别由它们自己的 id 表示，他们的进球由 host_goals 和 guest_goals 分别表示。

你希望在所有比赛之后计算所有球队的比分。积分奖励方式如下:

  • 如果球队赢了比赛(即比对手进更多的球)，就得 3 分。
  • 如果双方打成平手(即，与对方得分相同)，则得 1 分。
  • 如果球队输掉了比赛(例如，比对手少进球)，就 不得分 。

编写解决方案，以找出每个队的 team_id，team_name 和 num_points。

返回的结果根据 num_points 降序排序，如果有两队积分相同，那么这两队按 team_id 升序排序。

返回结果格式如下。

示例 1:

输入：
Teams table:
+-----------+--------------+
| team_id   | team_name    |
+-----------+--------------+
| 10        | Leetcode FC  |
| 20        | NewYork FC   |
| 30        | Atlanta FC   |
| 40        | Chicago FC   |
| 50        | Toronto FC   |
+-----------+--------------+
Matches table:
+------------+--------------+---------------+-------------+--------------+
| match_id   | host_team    | guest_team    | host_goals  | guest_goals  |
+------------+--------------+---------------+-------------+--------------+
| 1          | 10           | 20            | 3           | 0            |
| 2          | 30           | 10            | 2           | 2            |
| 3          | 10           | 50            | 5           | 1            |
| 4          | 20           | 30            | 1           | 0            |
| 5          | 50           | 30            | 1           | 0            |
+------------+--------------+---------------+-------------+--------------+
输出：
+------------+--------------+---------------+
| team_id    | team_name    | num_points    |
+------------+--------------+---------------+
| 10         | Leetcode FC  | 7             |
| 20         | NewYork FC   | 3             |
| 50         | Toronto FC   | 3             |
| 30         | Atlanta FC   | 1             |
| 40         | Chicago FC   | 0             |
+------------+--------------+---------------+

解答：


SELECT t.team_id,
       t.team_name,
       IFNULL(SUM(score), 0) AS num_points
FROM Teams t
LEFT JOIN (
    SELECT host_team AS team_id,
           CASE 
               WHEN host_goals > guest_goals THEN 3
               WHEN host_goals = guest_goals THEN 1
               ELSE 0 
           END AS score
    FROM Matches
    UNION ALL
    SELECT guest_team AS team_id,
           CASE 
               WHEN guest_goals > host_goals THEN 3
               WHEN guest_goals = host_goals THEN 1
               ELSE 0 
           END AS score
    FROM Matches
) m ON t.team_id = m.team_id
GROUP BY t.team_id, t.team_name
ORDER BY num_points DESC, t.team_id;

  • 没有结果（null）则返回0——>IFNULL(col, 0)

  • 思路解析

    1. UNION ALL
      • 第一段取主队（host_team），根据比分计算积分。
      • 第二段取客队（guest_team），同样计算积分。
      • 这样每场比赛会贡献两行数据（主队、客队）。
    2. 外层再 JOIN Teams → 确保所有球队都显示，即使一场没打过。
    3. GROUP BY 聚合积分，ORDER BY num_points DESC, team_id 保证排序符合要求。

1445. 苹果和桔子

表: Sales

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| sale_date     | date    |
| fruit         | enum    | 
| sold_num      | int     | 
+---------------+---------+
(sale_date, fruit) 是该表主键(具有唯一值的列的组合)。
该表包含了每一天中"苹果" 和 "桔子"的销售情况。

编写解决方案报告每一天 苹果 和 桔子 销售的数目的差异.

返回的结果表, 按照格式为 (‘YYYY-MM-DD’) 的 sale_date 排序.

返回结果表如下例所示:

示例 1：

输入：
Sales 表:
+------------+------------+-------------+
| sale_date  | fruit      | sold_num    |
+------------+------------+-------------+
| 2020-05-01 | apples     | 10          |
| 2020-05-01 | oranges    | 8           |
| 2020-05-02 | apples     | 15          |
| 2020-05-02 | oranges    | 15          |
| 2020-05-03 | apples     | 20          |
| 2020-05-03 | oranges    | 0           |
| 2020-05-04 | apples     | 15          |
| 2020-05-04 | oranges    | 16          |
+------------+------------+-------------+
输出：
+------------+--------------+
| sale_date  | diff         |
+------------+--------------+
| 2020-05-01 | 2            |
| 2020-05-02 | 0            |
| 2020-05-03 | 20           |
| 2020-05-04 | -1           |
+------------+--------------+
解释：
在 2020-05-01, 卖了 10 个苹果 和 8 个桔子 (差异为 10 - 8 = 2).
在 2020-05-02, 卖了 15 个苹果 和 15 个桔子 (差异为 15 - 15 = 0).
在 2020-05-03, 卖了 20 个苹果 和 0 个桔子 (差异为 20 - 0 = 20).
在 2020-05-04, 卖了 15 个苹果 和 16 个桔子 (差异为 15 - 16 = -1).

解答：

select a.sale_date, a.sold_num - b.sold_num as diff
from Sales a left join Sales b
on a.sale_date = b.sale_date 
where a.fruit = 'apples' and b.fruit = 'oranges';

自连接数据表
把 Sales 表取两份：一份叫 a，一份叫 b，然后通过 a.sale_date = b.sale_date 把同一天的苹果和橘子配对到一起。

限定水果种类
在 where 子句里规定：a.fruit = 'apples'，b.fruit = 'oranges'，这样就保证 a 表只取苹果的销售数据，b 表只取橘子的销售数据。

计算销售差值
在 select 里取出日期 (a.sale_date)、苹果销量 (a.sold_num)、橘子销量 (b.sold_num)，并且计算它们的差值 a.sold_num - b.sold_num，命名为 diff。

1699.两人之间的通话次数

表： Calls

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| from_id     | int     |
| to_id       | int     |
| duration    | int     |
+-------------+---------+
该表没有主键(具有唯一值的列)，它可能包含重复项。
该表包含 from_id 与 to_id 间的一次电话的时长。
from_id != to_id

编写解决方案，统计每一对用户 (person1, person2) 之间的通话次数和通话总时长，其中 person1 < person2 。

以 任意顺序 返回结果表。

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

示例 1：

输入：
Calls 表：
+---------+-------+----------+
| from_id | to_id | duration |
+---------+-------+----------+
| 1       | 2     | 59       |
| 2       | 1     | 11       |
| 1       | 3     | 20       |
| 3       | 4     | 100      |
| 3       | 4     | 200      |
| 3       | 4     | 200      |
| 4       | 3     | 499      |
+---------+-------+----------+
输出：
+---------+---------+------------+----------------+
| person1 | person2 | call_count | total_duration |
+---------+---------+------------+----------------+
| 1       | 2       | 2          | 70             |
| 1       | 3       | 1          | 20             |
| 3       | 4       | 4          | 999            |
+---------+---------+------------+----------------+
解释：
用户 1 和 2 打过 2 次电话，总时长为 70 (59 + 11)。
用户 1 和 3 打过 1 次电话，总时长为 20。
用户 3 和 4 打过 4 次电话，总时长为 999 (100 + 200 + 200 + 499)。

解答：

select a as person1, b as person2, sum(total_count) as call_count, sum(total) as total_duration from(
select from_id as a, to_id as b, count(*) as total_count, sum(duration) as total from Calls group by
from_id, to_id 
union all
select to_id as a, from_id as b, count(*) as total_count, sum(duration) as total from Calls group by
to_id, from_id
)t where a < b group by a, b;

正反向通话拆开统计
先用 from_id → to_id 分组统计每一对通话的次数和总时长，再用 to_id → from_id 反向统计。
这样做的目的是把双方互相拨打的情况都纳入考虑。

union all 合并结果
把正向和反向的结果拼在一起，形成一张“对称”的结果集。
此时，每一对用户 (person1, person2) 会出现两次：一条是正向统计，一条是反向统计。

统一顺序并再次聚合
在外层查询里，用 where a < b 保证只保留 (小id, 大id) 的组合，消除顺序带来的重复。
然后再按 (a, b) 分组，用 sum(total_count) 和 sum(total) 汇总双方的通话次数和总时长。

1501.可以放心投资的国家

表 Person:

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| id             | int     |
| name           | varchar |
| phone_number   | varchar |
+----------------+---------+
id 是该表具有唯一值的列.
该表每一行包含一个人的名字和电话号码.
电话号码的格式是:'xxx-yyyyyyy', 其中 xxx 是国家码(3 个字符), yyyyyyy 是电话号码(7 个字符), x 和 y 都表示数字. 同时, 国家码和电话号码都可以包含前导 0.

表 Country:

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| name           | varchar |
| country_code   | varchar |
+----------------+---------+
country_code 是该表具有唯一值的列.
该表每一行包含国家名和国家码. country_code 的格式是'xxx', x 是数字.

表 Calls:

+-------------+------+
| Column Name | Type |
+-------------+------+
| caller_id   | int  |
| callee_id   | int  |
| duration    | int  |
+-------------+------+
该表无主键, 可能包含重复行.
每一行包含呼叫方 id, 被呼叫方 id 和以分钟为单位的通话时长. caller_id != callee_id

一家电信公司想要投资新的国家。该公司想要投资的国家是: 该国的平均通话时长要严格地大于全球平均通话时长。

写一个解决方案, 找到所有该公司可以投资的国家。

返回的结果表 无顺序要求。

结果格式如下例所示。

示例 1:

输入：
Person 表：
+----+----------+--------------+
| id | name     | phone_number |
+----+----------+--------------+
| 3  | Jonathan | 051-1234567  |
| 12 | Elvis    | 051-7654321  |
| 1  | Moncef   | 212-1234567  |
| 2  | Maroua   | 212-6523651  |
| 7  | Meir     | 972-1234567  |
| 9  | Rachel   | 972-0011100  |
+----+----------+--------------+
Country 表:
+----------+--------------+
| name     | country_code |
+----------+--------------+
| Peru     | 051          |
| Israel   | 972          |
| Morocco  | 212          |
| Germany  | 049          |
| Ethiopia | 251          |
+----------+--------------+
Calls 表:
+-----------+-----------+----------+
| caller_id | callee_id | duration |
+-----------+-----------+----------+
| 1         | 9         | 33       |
| 2         | 9         | 4        |
| 1         | 2         | 59       |
| 3         | 12        | 102      |
| 3         | 12        | 330      |
| 12        | 3         | 5        |
| 7         | 9         | 13       |
| 7         | 1         | 3        |
| 9         | 7         | 1        |
| 1         | 7         | 7        |
+-----------+-----------+----------+
输出：
+----------+
| country  |
+----------+
| Peru     |
+----------+
解释：
国家 Peru 的平均通话时长是 (102 + 102 + 330 + 330 + 5 + 5) / 6 = 145.666667
国家 Israel 的平均通话时长是 (33 + 4 + 13 + 13 + 3 + 1 + 1 + 7) / 8 = 9.37500
国家 Morocco 的平均通话时长是 (33 + 4 + 59 + 59 + 3 + 7) / 6 = 27.5000 
全球平均通话时长 = (2 * (33 + 4 + 59 + 102 + 330 + 5 + 13 + 3 + 1 + 7)) / 20 = 55.70000
所以, Peru 是唯一的平均通话时长大于全球平均通话时长的国家, 也是唯一的推荐投资的国家.

解答：

一个表（Calls）中出现两列（caller_id 、callee_id）都是同属一个表（Person）里的属性，用join+union即可全部归纳

with base as(
    select c1.caller_id as id, c1.duration as phonetime, p1.phone_number as number from Calls c1 join Person p1 on c1.caller_id = p1.id 
    union all
    select c2.callee_id as id, c2.duration as phonetime, p2.phone_number as number from Calls c2 join Person p2 on c2.callee_id = p2.id 
),
seq as(
    select a.id, a.phonetime, b.name as country_name from base a join Country b on substring(a.number, 1, 3) = b.country_code 
),
country_avg as (
    select country_name, avg(phonetime) as avg_time from seq group by country_name
),
global_avg as (
    select avg(phonetime) as avg_global from seq 
)
select country_name as country from country_avg where avg_time > (
    select avg_global from global_avg
  1. 提取国家代码：从Person表中提取每个用户的国家代码（电话号码的前三个字符）。
  2. 计算每个通话的国家：对于Calls表中的每个通话，无论是呼叫者还是被呼叫者，都需要确定其国家代码，从而将通话时长分配给相应的国家。
  3. 计算每个国家的平均通话时长：汇总每个国家的所有通话时长，计算其平均值。
  4. 计算全球平均通话时长：汇总所有通话的通话时长，计算全球平均值。
  5. 比较并筛选国家：筛选出那些平均通话时长严格大于全球平均通话时长的国家。

1264.页面描述

朋友关系列表： Friendship

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user1_id      | int     |
| user2_id      | int     |
+---------------+---------+
(user1_id, user2_id) 是这张表具有唯一值的列的组合。
这张表的每一行代表着 user1_id 和 user2_id 之间存在着朋友关系。

喜欢列表： Likes

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| page_id     | int     |
+-------------+---------+
(user_id, page_id) 是这张表具有唯一值的列的组合。
这张表的每一行代表着 user_id 喜欢 page_id。

编写解决方案，向user_id = 1 的用户，推荐其朋友们喜欢的页面。不要推荐该用户已经喜欢的页面。

以 任意顺序 返回结果，其中不应当包含重复项。

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

示例 1:

输入：
Friendship table:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1        | 2        |
| 1        | 3        |
| 1        | 4        |
| 2        | 3        |
| 2        | 4        |
| 2        | 5        |
| 6        | 1        |
+----------+----------+
 
Likes table:
+---------+---------+
| user_id | page_id |
+---------+---------+
| 1       | 88      |
| 2       | 23      |
| 3       | 24      |
| 4       | 56      |
| 5       | 11      |
| 6       | 33      |
| 2       | 77      |
| 3       | 77      |
| 6       | 88      |
+---------+---------+

输出：
+------------------+
| recommended_page |
+------------------+
| 23               |
| 24               |
| 56               |
| 33               |
| 77               |
+------------------+
解释：
用户1 同 用户2, 3, 4, 6 是朋友关系。
推荐页面为： 页面23 来自于 用户2, 页面24 来自于 用户3, 页面56 来自于 用户3 以及 页面33 来自于 用户6。
页面77 同时被 用户2 和 用户3 推荐。
页面88 没有被推荐，因为 用户1 已经喜欢了它。

解答：

with base as(
    select case when user1_id = 1 then user2_id 
    when user2_id = 1 then user1_id else null end as friend from Friendship
),
seq as(
    select distinct a.page_id from Likes a join base b on a.user_id = b.friend where b.friend is not null
)
select page_id as recommended_page from seq a where not exists (
    select b.page_id from Likes b where b.user_id = 1 and a.page_id = b.page_id
);

如果等值子查询会返回null，则需要使用not exists+相关子查询

# leetcode # sql # 数据库
Logo
乐奇 Rokid 开放社区

更多推荐

cover

让界面替我“摇“一次：一个AR祈愿智能体的AIUI实践

avatar 乐奇 Rokid 开放社区
cover

极刻觅镜 | AI眼镜日报｜Warby Parker智能眼镜开启预订，苹果公开隐私专利

avatar 乐奇 Rokid 开放社区
cover

极刻觅镜 | AI眼镜日报｜三星、Warby Parker 预售定档，影目与罗敦司德披露技术进展

avatar 乐奇 Rokid 开放社区