写法比较简单,也比较灵活,但是只适用于MySQL8.0及以上版本
WITH RECURSIVE 语法
WITH recursive 表名 AS (
初始语句(非递归部分)
UNION ALL
递归部分语句
)
[ SELECT| INSERT | UPDATE | DELETE]
with recursive 由两部分组成。第一部分是非递归部分( union all 上方),第二部分是递归部分(union all下方)。递归部分第一次进入的时候使用非递归部分传递过来的参数,也就是第一行的数据值,进而得到第二行数据值,然后根据第二行数据值得到第三行数据值。
案例1:
#1到5计数。
WITH RECURSIVE t(n) AS (
SELECT 1 # 先执行,执行后t表有一条记录1,字段名n
UNION ALL
SELECT n+1 FROM t WHERE n < 5 #后执行,t表的记录作为传入表,执行后生成第二条记录2(n=1的前提
#下),成为新的t表,通过union all 与之前的结果合并成新的结果
#表,然后重复执行该语句,新的t表作为传入表提供数据,
)
SELECT * FROM t;#新的t表生成,n=5时,递归结束,使用该语句把union all所有递归结果的结果表查询出
#来,当然也可以对结果表做相应操作,例如left join ,sum()等
案例2:寻找没有被执行的任务对
表:
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。
请试写一个SQL查询语句报告没有被执行的(主任务,子任务)对,即没有被执行的(task_id, subtask_id)。
示例
输入: 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,我们不返回任何值。
with recursive t(task_id, subtask_id) as (
SELECT task_id, subtasks_count FROM Tasks #获取原始t表,作为数据传入递归部分
UNION ALL
SELECT task_id, subtask_id-1 FROM t where subtask_id-1>0#基于原始t表执行语句,生成新的t
#表,新的t表union all成结果表的
#同时,作为下一次执行递归语句的传
#入数据
)
SELECT t.* FROM t
left join Executed t1
on t.task_id=t1.task_id and t.subtask_id=t1.subtask_id
WHERE t1.subtask_id is null
ORDER BY t.task_id, t.subtask_id