mysql cte_MySQL 8 列出相关依赖 使用CTE(公共数据表达式)

直接 SQL 实现递归的 with 语法——公共数据表达式 Common Table Expressions (CTE) 是一个命名的临时结果集，它存在于单个语句的范围内，并可能在该语句后面引用，可能会多次。

# 创建测试表

create table job_depend(

sn_id int auto_increment comment '行号'

primary key,

job_id varchar(20) null comment '作业ID',

depend_job_id varchar(20) null comment '依赖作业'

)comment '作业依赖';

# 插入测试数据

INSERT INTO job_depend (job_id, depend_job_id) VALUES ('b', 'a');

INSERT INTO job_depend (job_id, depend_job_id) VALUES ('c', 'b');

INSERT INTO job_depend (job_id, depend_job_id) VALUES ('d', 'b');

INSERT INTO job_depend (job_id, depend_job_id) VALUES ('e', 'c');

# CTE 查询所有父代和子代 RECURSIVE(递归)

WITH RECURSIVE

parent(job_id, depend_job_id, level) AS(

SELECT job_id, depend_job_id, 0 AS level

FROM job_depend WHERE job_id = 'a'

UNION ALL

SELECT A.job_id, A.depend_job_id, level - 1

FROM job_depend A JOIN parent B ON A.job_id = B.depend_job_id

),

child(job_id, depend_job_id, level) AS(

SELECT job_id, depend_job_id, 0 AS level

FROM job_depend WHERE depend_job_id = 'a'

UNION ALL

SELECT A.job_id, A.depend_job_id, level + 1

FROM job_depend A JOIN child B ON A.depend_job_id = B.job_id

)

SELECT * FROM parent

UNION

SELECT * FROM child

ORDER BY level;