1767.(Hard)Find the Subtasks That Did Not Execute

Table: Tasks

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| task_id        | int     |
| subtasks_count | int     |
+----------------+---------+
task_id is the primary key for this table.
Each row in this table indicates that task_id was divided into subtasks_count subtasks labelled from 1 to subtasks_count.
It is guaranteed that 2 <= subtasks_count <= 20.

Table: Executed

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| task_id       | int     |
| subtask_id    | int     |
+---------------+---------+
(task_id, subtask_id) is the primary key for this table.
Each row in this table indicates that for the task task_id, the subtask with ID subtask_id was executed successfully.
It is guaranteed that subtask_id <= subtasks_count for each task_id.

Write an SQL query to report the IDs of the missing subtasks for each task_id.

Return the result table in any order.

The query result format is in the following example:

Tasks table:
+---------+----------------+
| task_id | subtasks_count |
+---------+----------------+
| 1       | 3              |
| 2       | 2              |
| 3       | 4              |
+---------+----------------+

Executed table:
+---------+------------+
| task_id | subtask_id |
+---------+------------+
| 1       | 2          |
| 3       | 1          |
| 3       | 2          |
| 3       | 3          |
| 3       | 4          |
+---------+------------+

Result table:
+---------+------------+
| task_id | subtask_id |
+---------+------------+
| 1       | 1          |
| 1       | 3          |
| 2       | 1          |
| 2       | 2          |
+---------+------------+
Task 1 was divided into 3 subtasks (1, 2, 3). Only subtask 2 was executed successfully, so we include (1, 1) and (1, 3) in the answer.
Task 2 was divided into 2 subtasks (1, 2). No subtask was executed successfully, so we include (2, 1) and (2, 2) in the answer.
Task 3 was divided into 4 subtasks (1, 2, 3, 4). All of the subtasks were executed successfully.

来源：力扣（LeetCode）

链接：https://leetcode-cn.com/problems/find-the-subtasks-that-did-not-execute

著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

Solution

with recursive t as 
( 
SELECT task_id, subtasks_count from Tasks 
union
SELECT task_id, subtasks_count-1 subtask
from t where subtasks_count-1 !=0 
)
SELECT task_id, subtasks_count subtask_id from t
where (task_id, subtasks_count) not in (SELECT task_id, subtask_id from Executed);

Table Schema

Create table If Not Exists Tasks (task_id int, subtasks_count int);
Create table If Not Exists Executed (task_id int, subtask_id int);
Truncate table Tasks;
insert into Tasks (task_id, subtasks_count) values ('1', '3');
insert into Tasks (task_id, subtasks_count) values ('2', '2');
insert into Tasks (task_id, subtasks_count) values ('3', '4');
Truncate table Executed;
insert into Executed (task_id, subtask_id) values ('1', '2');
insert into Executed (task_id, subtask_id) values ('3', '1');
insert into Executed (task_id, subtask_id) values ('3', '2');
insert into Executed (task_id, subtask_id) values ('3', '3');
insert into Executed (task_id, subtask_id) values ('3', '4');