使用递归CTE生成作业依赖层级列表的问题排查与解决

问题背景 #

现有CTL_TALEND_JOBS_METADATA作业元数据表，包含JOB_NO（主作业编号）、DEPENDANT_JOB_NO（依赖作业编号）、DEPENDANT_SUB_JOB_NO（依赖子作业编号）字段，需通过递归CTE生成包含主作业、依赖作业、依赖子作业的完整层级依赖列表（以Job_198为例）。

常见错误原因 #

多数不符合预期的递归CTE存在以下问题：

• 关联条件仅绑定DEPENDANT_JOB_NO，忽略DEPENDANT_SUB_JOB_NO作为下一级递归起点的情况
• 未处理循环依赖，导致递归死循环或结果重复
• 层级跟踪、依赖路径拼接逻辑错误，无法清晰展示依赖链

正确递归CTE实现 #

WITH RECURSIVE job_dependencies AS (
    -- 锚点：初始化目标主作业的基础信息
    SELECT
        JOB_NO AS main_job,
        JOB_NO AS current_job,
        DEPENDANT_JOB_NO AS dependent_job,
        DEPENDANT_SUB_JOB_NO AS dependent_sub_job,
        1 AS hierarchy_level,
        CAST(JOB_NO AS VARCHAR(1000)) AS dependency_path
    FROM CTL_TALEND_JOBS_METADATA
    WHERE JOB_NO = 'Job_198'

    UNION ALL

    -- 递归：遍历所有层级的依赖关系
    SELECT
        jd.main_job,
        -- 优先以子作业作为下一级递归的当前作业，无则用依赖作业
        COALESCE(ctm.DEPENDANT_SUB_JOB_NO, ctm.DEPENDANT_JOB_NO) AS current_job,
        ctm.DEPENDANT_JOB_NO AS dependent_job,
        ctm.DEPENDANT_SUB_JOB_NO AS dependent_sub_job,
        jd.hierarchy_level + 1 AS hierarchy_level,
        -- 拼接完整依赖路径
        CONCAT(jd.dependency_path, ' -> ', COALESCE(ctm.DEPENDANT_SUB_JOB_NO, ctm.DEPENDANT_JOB_NO)) AS dependency_path
    FROM job_dependencies jd
    JOIN CTL_TALEND_JOBS_METADATA ctm
        -- 关联上一级的依赖作业/子作业，作为当前递归的主作业
        ON ctm.JOB_NO = COALESCE(jd.dependent_sub_job, jd.dependent_job)
    -- 避免循环依赖导致死循环
    WHERE NOT jd.dependency_path LIKE CONCAT('%', COALESCE(ctm.DEPENDANT_SUB_JOB_NO, ctm.DEPENDANT_JOB_NO), '%')
)
-- 整理输出层级化依赖列表
SELECT
    main_job,
    hierarchy_level,
    dependency_path,
    dependent_job,
    dependent_sub_job
FROM job_dependencies
ORDER BY hierarchy_level, dependency_path;

关键逻辑说明 #

1. 锚点成员：直接定位目标主作业，初始化层级为1，依赖路径从主作业自身开始。
2. 递归关联：通过COALESCE优先处理子作业的依赖传递，确保所有层级的作业/子作业都被遍历。
3. 循环防护：通过依赖路径判断，避免同一作业重复进入递归链。
4. 输出字段：包含层级编号、完整依赖路径、对应依赖作业/子作业，直观展示整个依赖树结构。

内容的提问来源于stack exchange，提问作者Arjun M