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;

MySQL 8 两个新特性
公共数据表达式（临时结果集）官方文档：
https://dev.mysql.com/doc/refman/8.0/en/with.html
窗口函数（逐行统计函数）官方文档：
(不用 group by 而在每行显示类似 sum() 等结果，用来实现排名和占比等功能)
https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html