力扣高频 SQL50 题（进阶版）重点部分

购买了产品 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 customer_id,customer_name 
from customers 
where customer_id in (select customer_id from orders where product_name='A') 
and customer_id in (select customer_id from orders where product_name='B') 
and customer_id not in (select customer_id from orders where product_name='C')
order by customer_id;

2.having 判断
select a.customer_id,b.customer_name 
from orders a 
left join customers b on a.customer_id=b.customer_id
group by a.customer_id 
having sum(if(product_name='A',1,0))*sum(if(product_name='B',1,0))>0 
and sum(if(product_name='C',1,0))=0;

计算布尔表达式的值

表 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 表中的每一个布尔表达式的值.

# 两个join和case when
# 注意true 和false 是字符串
# 有两个都要关联，所以使用两个join
select a.left_operand,a.operator,a.right_operand,
case when a.operator ='>' and b.value > c.value then 'true' 
when a.operator='<' and b.value < c.value then 'true' 
when a.operator='=' and b.value=c.value then 'true' else 'false' end value 
from expressions a 
left join variables b on b.name=a.left_operand 
left join variables c on c.name=a.right_operand;

查询球队积分

表: 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             |
+------------+--------------+---------------+

# 简单容易理解
# 注意是所有的球队都要展示出来，null的处理问题
# union all 和join
select a.team_id,a.team_name,ifnull(sum(b.score),0) num_points 
from teams a 
left join (select host_team,case when host_goals > guest_goals then 3 when host_goals < guest_goals then 0 else 1 end score
from matches 
union all 
select guest_team,case when host_goals > guest_goals then 0 when host_goals < guest_goals then 3 else 1 end score
from matches) b on a.team_id=b.host_team 
group by a.team_id,a.team_name
order by num_points desc,team_id ;

# 先连接再计算
# 注意case when 的时候判断主客队情况
SELECT 
    team_id,
    team_name,
    IFNULL(SUM(
        CASE
            WHEN host_team = team_id AND host_goals > guest_goals THEN 3
            WHEN guest_team = team_id AND host_goals < guest_goals THEN 3
            WHEN host_goals = guest_goals THEN 1
            ELSE 0
        END
        ), 0) num_points
FROM Teams
LEFT JOIN Matches
ON Matches.host_team = team_id OR Matches.guest_team = team_id
GROUP BY team_id
ORDER BY num_points DESC, team_id ASC

# 中间步骤，更加容易理解
select a.team_id,a.team_name,b.host_team,b.guest_team,host_goals,guest_goals
from Teams a LEFT JOIN Matches b 
ON b.host_team = a.team_id OR b.guest_team = a.team_id;

 team_id | team_name   | host_team | guest_team | host_goals | guest_goals |
| ------- | ----------- | --------- | ---------- | ---------- | ----------- |
| 10      | Leetcode FC | 10        | 50         | 5          | 1           |
| 10      | Leetcode FC | 30        | 10         | 2          | 2           |
| 10      | Leetcode FC | 10        | 20         | 3          | 0           |
| 20      | NewYork FC  | 20        | 30         | 1          | 0           |
| 20      | NewYork FC  | 10        | 20         | 3          | 0           |
| 30      | Atlanta FC  | 50        | 30         | 1          | 0           |
| 30      | Atlanta FC  | 20        | 30         | 1          | 0           |
| 30      | Atlanta FC  | 30        | 10         | 2          | 2           |
| 40      | Chicago FC  | null      | null       | null       | null        |
| 50      | Toronto FC  | 50        | 30         | 1          | 0           |
| 50      | Toronto FC  | 10        | 50         | 5          | 1           ...

两人之间的通话次数

表： 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)。

# 使用case when 确保person1小，person2大
select case when from_id < to_id then from_id else to_id end person1,
case when from_id > to_id then from_id else to_id end person2,
count(1) call_count,sum(duration) total_duration 
from calls 
group by person1,person2;

消费者下单频率

表: Customers

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| name          | varchar |
| country       | varchar |
+---------------+---------+
customer_id 是该表具有唯一值的列.
该表包含公司消费者的信息.
 

表: Product

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| description   | varchar |
| price         | int     |
+---------------+---------+
product_id 是该表具有唯一值的列.
该表包含公司产品的信息.
price 是该产品所需的花销.
 

表: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| customer_id   | int     |
| product_id    | int     |
| order_date    | date    |
| quantity      | int     |
+---------------+---------+
order_id 是该表具有唯一值的列.
该表包含消费者下单的信息.
customer_id 是买了数量为 "quantity", id 为 "product_id" 产品的消费者的 id.
Order_date 是订单发货的日期, 格式为('YYYY-MM-DD').
 

写一个解决方案，报告在 2020 年 6 月和 7 月 每个月至少花费 $100 的客户的 customer_id 和 customer_name 。

以 任意顺序 返回结果表.

结果格式如下例所示。

 

示例 1:

输入：
Customers table:
+--------------+-----------+-------------+
| customer_id  | name      | country     |
+--------------+-----------+-------------+
| 1            | Winston   | USA         |
| 2            | Jonathan  | Peru        |
| 3            | Moustafa  | Egypt       |
+--------------+-----------+-------------+
Product table:
+--------------+-------------+-------------+
| product_id   | description | price       |
+--------------+-------------+-------------+
| 10           | LC Phone    | 300         |
| 20           | LC T-Shirt  | 10          |
| 30           | LC Book     | 45          |
| 40           | LC Keychain | 2           |
+--------------+-------------+-------------+
Orders table:
+--------------+-------------+-------------+-------------+-----------+
| order_id     | customer_id | product_id  | order_date  | quantity  |
+--------------+-------------+-------------+-------------+-----------+
| 1            | 1           | 10          | 2020-06-10  | 1         |
| 2            | 1           | 20          | 2020-07-01  | 1         |
| 3            | 1           | 30          | 2020-07-08  | 2         |
| 4            | 2           | 10          | 2020-06-15  | 2         |
| 5            | 2           | 40          | 2020-07-01  | 10        |
| 6            | 3           | 20          | 2020-06-24  | 2         |
| 7            | 3           | 30          | 2020-06-25  | 2         |
| 9            | 3           | 30          | 2020-05-08  | 3         |
+--------------+-------------+-------------+-------------+-----------+
输出：
+--------------+------------+
| customer_id  | name       |  
+--------------+------------+
| 1            | Winston    |
+--------------+------------+ 
解释：
Winston 在 2020 年 6 月花费了 $300(300 * 1), 在 7 月花费了 $100(10 * 1 + 45 * 2).
Jonathan 在 2020 年 6 月花费了 $600(300 * 2), 在 7 月花费了 $20(2 * 10).
Moustafa 在 2020 年 6 月花费了 $110 (10 * 2 + 45 * 2), 在 7 月花费了 $0.

# 考察sum if 
select c.customer_id,c.name 
from (select * from orders where left(order_date,7) between '2020-06' and '2020-07') a 
left join product b on a.product_id=b.product_id 
left join customers c on a.customer_id=c.customer_id 
group by c.customer_id,c.name
having sum(if(month(order_date)=6,a.quantity*b.price,0)) >=100 
and sum(if(month(order_date)=7,a.quantity*b.price,0)) >=100 ;

连续空余座位

表: Cinema

+-------------+------+
| Column Name | Type |
+-------------+------+
| seat_id     | int  |
| free        | bool |
+-------------+------+
Seat_id 是该表的自动递增主键列。
在 PostgreSQL 中，free 存储为整数。请使用 ::boolean 将其转换为布尔格式。
该表的每一行表示第 i 个座位是否空闲。1 表示空闲，0 表示被占用。
 

查找电影院所有连续可用的座位。

返回按 seat_id 升序排序 的结果表。

测试用例的生成使得两个以上的座位连续可用。

结果表格式如下所示。

 

示例 1:

输入: 
Cinema 表:
+---------+------+
| seat_id | free |
+---------+------+
| 1       | 1    |
| 2       | 0    |
| 3       | 1    |
| 4       | 1    |
| 5       | 1    |
+---------+------+
输出: 
+---------+
| seat_id |
+---------+
| 3       |
| 4       |
| 5       |
+---------+

# 辅助列 row_number 不需要知道n,不需要知道连续几个空余座位
select seat_id
from (select seat_id,count(1) over(partition by rn) cnt 
from (select seat_id,seat_id-row_number() over(order by seat_id) rn 
from cinema a where free=1) z ) z2
where cnt >=2 
order by seat_id;

# lag lead 函数 只使用一个有可能会漏掉
select seat_id 
from (select seat_id,lag(seat_id,1,-1) over(order by seat_id) lag_id,
lead(seat_id,1,-1) over(order by seat_id) lead_id 
from cinema a where free=1) z 
where seat_id-lag_id=1 or lead_id-seat_id=1 
order by seat_id;

直线上的最近距离

表： Point

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

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

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

 

示例 1：

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

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

# 自关联
select min(abs(a.x-b.x)) shortest 
from point a 
left join point b on a.x!=b.x;

丢失信息的雇员

表: Employees

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
+-------------+---------+
employee_id 是该表中具有唯一值的列。
每一行表示雇员的 id 和他的姓名。
表: Salaries

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| salary      | int     |
+-------------+---------+
employee_id 是该表中具有唯一值的列。
每一行表示雇员的 id 和他的薪水。
 

编写解决方案，找到所有 丢失信息 的雇员 id。当满足下面一个条件时，就被认为是雇员的信息丢失：

雇员的 姓名 丢失了，或者
雇员的 薪水信息 丢失了
返回这些雇员的 id  employee_id ， 从小到大排序 。

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

 

示例 1：

输入：
Employees table:
+-------------+----------+
| employee_id | name     |
+-------------+----------+
| 2           | Crew     |
| 4           | Haven    |
| 5           | Kristian |
+-------------+----------+
Salaries table:
+-------------+--------+
| employee_id | salary |
+-------------+--------+
| 5           | 76071  |
| 1           | 22517  |
| 4           | 63539  |
+-------------+--------+
输出：
+-------------+
| employee_id |
+-------------+
| 1           |
| 2           |
+-------------+
解释：
雇员 1，2，4，5 都在这个公司工作。
1 号雇员的姓名丢失了。
2 号雇员的薪水信息丢失了。

# mysql 没有全外连接

select case when a.employee_id is null then b.employee_id else a.employee_id end employee_id 
from employees a 
full join salaries b on a.employee_id=b.employee_id 
where a.employee_id is null or b.employee_id is null 
order by employee_id ;

# 直接union all 如果两个都有employee_id的则，分组计数大于等于2
# 不全的则分组为1
select employee_id
from (select employee_id from employees 
union all 
select employee_id from salaries) a 
group by employee_id 
having count(1)=1
order by employee_id;

# 另一个方法则是union 之后，关联两个表，为null则是空的

向公司 CEO 汇报工作的所有人

员工表：Employees

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| employee_id   | int     |
| employee_name | varchar |
| manager_id    | int     |
+---------------+---------+
employee_id 是这个表具有唯一值的列。
这个表中每一行中，employee_id 表示职工的 ID，employee_name 表示职工的名字，manager_id 表示该职工汇报工作的直线经理。
这个公司 CEO 是 employee_id = 1 的人。
 

编写解决方案，找出所有直接或间接向公司 CEO 汇报工作的职工的 employee_id 。

由于公司规模较小，经理之间的间接关系 不超过 3 个经理 。

可以以 任何顺序 返回无重复项的结果。

返回结果示例如下。

 

示例 1：

输入：
Employees table:
+-------------+---------------+------------+
| employee_id | employee_name | manager_id |
+-------------+---------------+------------+
| 1           | Boss          | 1          |
| 3           | Alice         | 3          |



| 2           | Bob           | 1          |
| 4           | Daniel        | 2          |
| 7           | Luis          | 4          |
| 8           | Jhon          | 3          |
| 9           | Angela        | 8          |
| 77          | Robert        | 1          |
+-------------+---------------+------------+
输出：
+-------------+
| employee_id |
+-------------+
| 2           |
| 77          |
| 4           |
| 7           |
+-------------+
解释：
公司 CEO 的 employee_id 是 1.
employee_id 是 2 和 77 的职员直接汇报给公司 CEO。
employee_id 是 4 的职员间接汇报给公司 CEO 4 --> 2 --> 1 。
employee_id 是 7 的职员间接汇报给公司 CEO 7 --> 4 --> 2 --> 1 。
employee_id 是 3, 8 ，9 的职员不会直接或间接的汇报给公司 CEO。

# c.manager_id是三层后最终汇报的manager，最终要汇报给1，且不包含自己汇报的

select a.employee_id 
from employees a 
join employees b on a.manager_id=b.employee_id 
join employees c on b.manager_id=c.employee_id 
where a.employee_id<>1 and c.manager_id=1;

查找成绩处于中游的学生

表: Student

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| student_id          | int     |
| student_name        | varchar |
+---------------------+---------+
student_id 是该表主键(具有唯一值的列)。
student_name 学生名字。
 

表: Exam

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| exam_id       | int     |
| student_id    | int     |
| score         | int     |
+---------------+---------+
(exam_id, student_id) 是该表主键(具有唯一值的列的组合)。
学生 student_id 在测验 exam_id 中得分为 score。
 

成绩处于中游的学生是指至少参加了一次测验, 且得分既不是最高分也不是最低分的学生。

编写解决方案，找出在 所有 测验中都处于中游的学生 (student_id, student_name)。不要返回从来没有参加过测验的学生。

返回结果表按照 student_id 排序。

返回结果格式如下。

 

示例 1：

输入：
Student 表：
+-------------+---------------+
| student_id  | student_name  |
+-------------+---------------+
| 1           | Daniel        |
| 2           | Jade          |
| 3           | Stella        |
| 4           | Jonathan      |
| 5           | Will          |
+-------------+---------------+
Exam 表：
+------------+--------------+-----------+
| exam_id    | student_id   | score     |
+------------+--------------+-----------+
| 10         |     1        |    70     |
| 10         |     2        |    80     |
| 10         |     3        |    90     |
| 20         |     1        |    80     |
| 30         |     1        |    70     |
| 30         |     3        |    80     |
| 30         |     4        |    90     |
| 40         |     1        |    60     |
| 40         |     2        |    70     |
| 40         |     4        |    80     |
+------------+--------------+-----------+
输出：
+-------------+---------------+
| student_id  | student_name  |
+-------------+---------------+
| 2           | Jade          |
+-------------+---------------+
解释：
对于测验 1: 学生 1 和 3 分别获得了最低分和最高分。
对于测验 2: 学生 1 既获得了最高分, 也获得了最低分。
对于测验 3 和 4: 学生 1 和 4 分别获得了最低分和最高分。
学生 2 和 5 没有在任一场测验中获得了最高分或者最低分。
因为学生 5 从来没有参加过任何测验, 所以他被排除于结果表。
由此, 我们仅仅返回学生 2 的信息。

# 简单的容易理解的方法
# 1. 窗口函数找到每个科目中第一和倒数第一的学生，然后not in 即可
# 注意不能直接取中间的，因为是所有科目的中间学生，所以使用not in 
select a.student_id,b.student_name 
from exam a 
left join student b on a.student_id=b.student_id
where a.student_id not in (
    select student_id from (select exam_id,student_id,dense_rank() over(partition by exam_id order by score) dr_asc,
dense_rank() over(partition by exam_id order by score desc) dr_desc 
from exam) z where dr_asc =1 or dr_desc=1 group by student_id 
)
group by a.student_id,b.student_name  
order by a.student_id;

# 第二种方法
# 窗口函数 求每个科目的最小和最大分数，然后进行sum(if)判断

select a.student_id,b.student_name 
from (select *,min(score) over(partition by exam_id) min_score,
max(score) over(partition by exam_id) max_score from exam) a 
left join student b on a.student_id=b.student_id 
group by a.student_id,b.student_name 
having sum(if(score=min_score or score=max_score,1,0))=0
order by a.student_id
;

寻找没有被执行的任务对

表：Tasks

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| task_id        | int     |
| subtasks_count | int     |
+----------------+---------+
task_id 具有唯一值的列。
task_id 表示的为主任务的id,每一个task_id被分为了多个子任务(subtasks)，subtasks_count表示为子任务的个数（n），它的值表示了子任务的索引从1到n。
本表保证2 <=subtasks_count<= 20。
 

表： Executed

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| task_id       | int     |
| subtask_id    | int     |
+---------------+---------+
(task_id, subtask_id) 是该表中具有唯一值的列的组合。
每一行表示标记为task_id的主任务与标记为subtask_id的子任务被成功执行。
本表 保证 ，对于每一个task_id，subtask_id <= subtasks_count。
 

编写解决方案报告没有被执行的（主任务，子任务）对，即没有被执行的（task_id, subtask_id）。

以 任何顺序 返回即可。

查询结果格式如下。

 

示例 1：

输入：
Tasks 表:
+---------+----------------+
| task_id | subtasks_count |
+---------+----------------+
| 1       | 3              |
| 2       | 2              |
| 3       | 4              |
+---------+----------------+
Executed 表:
+---------+------------+
| task_id | subtask_id |
+---------+------------+
| 1       | 2          |
| 3       | 1          |
| 3       | 2          |
| 3       | 3          |
| 3       | 4          |
+---------+------------+
输出：
+---------+------------+
| task_id | subtask_id |
+---------+------------+
| 1       | 1          |
| 1       | 3          |
| 2       | 1          |
| 2       | 2          |
+---------+------------+
解释：
Task 1 被分成了 3 subtasks (1, 2, 3)。只有 subtask 2 被成功执行, 所以我们返回 (1, 1) 和 (1, 3) 这两个主任务子任务对。
Task 2 被分成了 2 subtasks (1, 2)。没有一个subtask被成功执行, 因此我们返回(2, 1)和(2, 2)。
Task 3 被分成了 4 subtasks (1, 2, 3, 4)。所有的subtask都被成功执行，因此对于Task 3,我们不返回任何值。

# 思路：1.把每个主任务补充完全，然后关联执行成功的子任务不相等的即为未执行的任务
# 重点是：如何把主任务补充完全

# 两种方法都要用到递归recursive
# 方法1：创造一个自增列，最大值为子任务最大编号
# 
with recursive tt(num) as (
    select 1 as num 
    union all
    select num+1 from tt where num<(select max(subtasks_count) from tasks)
)

select a.task_id,num as subtask_id
from tasks a 
left join tt b on b.num<=a.subtasks_count
left join executed c on a.task_id=c.task_id and b.num=c.subtask_id
where c.task_id is null;


# 方法2:直接根据任务和子任务最大编号补全任务和子任务记录
with recursive tt(task_id,subtask_id) as (
    select task_id,subtasks_count from tasks 
    union all
    select task_id,subtask_id-1 from tt 
    where subtask_id>=2
)

select * 
from tt a 
where (task_id,subtask_id) not in (select * from executed)

报告系统状态的连续日期

表：Failed

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| fail_date    | date    |
+--------------+---------+
该表主键为 fail_date (具有唯一值的列)。
该表包含失败任务的天数.
 

表： Succeeded

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| success_date | date    |
+--------------+---------+
该表主键为 success_date (具有唯一值的列)。
该表包含成功任务的天数.
 

系统 每天 运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或是成功。

编写解决方案找出 2019-01-01 到 2019-12-31 期间任务连续同状态 period_state 的起止日期（start_date 和 end_date）。即如果任务失败了，就是失败状态的起止日期，如果任务成功了，就是成功状态的起止日期。

最后结果按照起始日期 start_date 排序

返回结果样例如下所示:

 

示例 1：

输入：
Failed table:
+-------------------+
| fail_date         |
+-------------------+
| 2018-12-28        |
| 2018-12-29        |
| 2019-01-04        |
| 2019-01-05        |
+-------------------+
Succeeded table:
+-------------------+
| success_date      |
+-------------------+
| 2018-12-30        |
| 2018-12-31        |
| 2019-01-01        |
| 2019-01-02        |
| 2019-01-03        |
| 2019-01-06        |
+-------------------+
输出：
+--------------+--------------+--------------+
| period_state | start_date   | end_date     |
+--------------+--------------+--------------+
| succeeded    | 2019-01-01   | 2019-01-03   |
| failed       | 2019-01-04   | 2019-01-05   |
| succeeded    | 2019-01-06   | 2019-01-06   |
+--------------+--------------+--------------+
解释：
结果忽略了 2018 年的记录，因为我们只关心从 2019-01-01 到 2019-12-31 的记录
从 2019-01-01 到 2019-01-03 所有任务成功，系统状态为 "succeeded"。
从 2019-01-04 到 2019-01-05 所有任务失败，系统状态为 "failed"。
从 2019-01-06 到 2019-01-06 所有任务成功，系统状态为 "succeeded"。

# 使用开窗函数
# 1. 合并两个日期表，并添加辅助列
# 2. 求出连续日期的组，根据date_sub
# 3. 找出每组（状态和连续日期）的最开始日期和最后日期
# 4. select 出相对应的字段即可

select case when state=0 then 'failed' else 'succeeded' end period_state,
max(if(rn_asc=1,all_date,0)) start_date,max(if(rn_desc=1,all_date,0)) end_date 
from (select z.*,row_number() over(partition by state,date_diff order by all_date) rn_asc,
row_number() over(partition by state,date_diff order by all_date desc) rn_desc 
from (select all_date,state,date_sub(all_date,interval rn day) date_diff 
from (select fail_date all_date,0 state,row_number() over(order by fail_date) rn from failed 
union all 
select success_date all_date,1 state,row_number() over(order by success_date) rn from succeeded) a where all_date between '2019-01-01' and '2019-12-31') z) zz 
group by state,date_diff
order by start_date
;

# 优化了一下，直接进行分组求最小和最大日期即可，不使用开窗函数了，少一层嵌套
select case when state=0 then 'failed' else 'succeeded' end period_state,
min(all_date) start_date,max(all_date) end_date 
from (select all_date,state,date_sub(all_date,interval rn day) date_diff 
from (select fail_date all_date,0 state,row_number() over(order by fail_date) rn from failed 
union all 
select success_date all_date,1 state,row_number() over(order by success_date) rn from succeeded) a where all_date between '2019-01-01' and '2019-12-31') z 
group by state,date_diff
order by start_date;