with recursive案例讲解

with recursive案例讲解

+---------+----------------+
| task_id | subtasks_count |
+---------+----------------+
| 1       | 3              |
| 2       | 2              |
| 3       | 4              |
+---------+----------------+

需求：将其生成这个形式

+---------+----------------+
| task_id | subtasks_id    |
+---------+----------------+
| 1       | 1              |
| 1       | 2              |
| 1       | 3              |
| 2       | 1              |
| 2       | 2              |
| 3       | 1              |
| 3       | 2              |
| 3       | 3              |
| 3       | 4              |
+---------+----------------+

with RECURSIVE cte(task_id,subtasks_id) AS
(
  SELECT task_id,subtasks_count  from Tasks 
  UNION ALL
  SELECT task_id,subtasks_id - 1 FROM cte WHERE bbb >=2
)

SELECT * FROM cte;

解释：由于返回的表要求列名为task_id与subtasks_id，因此我们在cte后面的参数为(task_id,subtasks_id),如果将其换成其他的形式，sql的理解就会简便很多

aaa相对应的就是task_id，bbb想对应的就是subtasks_count

with RECURSIVE cte(aaa,bbb) AS
(
  SELECT task_id,subtasks_count  from Tasks 
  UNION ALL
  SELECT aaa,bbb - 1 FROM cte WHERE bbb >=2
)

SELECT * FROM cte;