基于SQL实现作业依赖关系排序与合并查询

1. 合并同一作业的直接前置 #

首先通过LISTAGG函数按调度和作业分组，将多个直接前置合并为逗号分隔的字符串，同时处理无前置的作业场景：

WITH job_preds AS (
    -- 获取所有作业，避免遗漏无前置记录的作业
    SELECT DISTINCT SCHEDULE, JOB FROM YOUR_TABLE
),
merged_preds AS (
    SELECT 
        jp.SCHEDULE,
        jp.JOB,
        -- 合并前置作业，空值转为空字符串
        NVL(LISTAGG(y.PREDECESSOR, ',') WITHIN GROUP (ORDER BY y.PREDECESSOR), '') AS PREDECESSORS
    FROM job_preds jp
    LEFT JOIN YOUR_TABLE y 
        ON jp.SCHEDULE = y.SCHEDULE 
        AND jp.JOB = y.JOB
    GROUP BY jp.SCHEDULE, jp.JOB
)
SELECT * FROM merged_preds;

这一步会得到每个作业的直接前置合并结果，比如示例中E的PREDECESSORS会是C,D（可通过调整ORDER BY字段改变前置的排序）。

2. 按依赖顺序（拓扑排序）输出作业 #

通过递归CTE实现拓扑排序，确保作业输出顺序符合依赖关系（前置作业先于依赖作业输出）：

WITH job_preds AS (
    SELECT DISTINCT SCHEDULE, JOB FROM YOUR_TABLE
),
merged_preds AS (
    SELECT 
        jp.SCHEDULE,
        jp.JOB,
        NVL(LISTAGG(y.PREDECESSOR, ',') WITHIN GROUP (ORDER BY y.PREDECESSOR), '') AS PREDECESSORS,
        -- 统计前置作业数量，用于识别无前置的初始节点
        COUNT(y.PREDECESSOR) AS pred_count
    FROM job_preds jp
    LEFT JOIN YOUR_TABLE y 
        ON jp.SCHEDULE = y.SCHEDULE 
        AND jp.JOB = y.JOB
    GROUP BY jp.SCHEDULE, jp.JOB
),
topology AS (
    -- 初始节点：无前置的作业
    SELECT 
        SCHEDULE,
        JOB,
        PREDECESSORS,
        1 AS execution_level
    FROM merged_preds
    WHERE pred_count = 0

    UNION ALL

    -- 递归获取所有前置已完成的作业
    SELECT 
        mp.SCHEDULE,
        mp.JOB,
        mp.PREDECESSORS,
        t.execution_level + 1 AS execution_level
    FROM merged_preds mp
    JOIN topology t 
        ON mp.SCHEDULE = t.SCHEDULE
    -- 校验当前作业的所有前置都已在拓扑结果中
    WHERE NOT EXISTS (
        SELECT 1 
        FROM (
            -- 拆分逗号分隔的前置列表为单个作业
            SELECT REGEXP_SUBSTR(mp.PREDECESSORS, '[^,]+', 1, LEVEL) AS pred_job
            FROM dual
            CONNECT BY LEVEL <= REGEXP_COUNT(mp.PREDECESSORS, ',') + 1
            WHERE mp.PREDECESSORS IS NOT NULL AND mp.PREDECESSORS != ''
        ) preds
        WHERE NOT EXISTS (
            SELECT 1 FROM topology t2 
            WHERE t2.SCHEDULE = mp.SCHEDULE 
            AND t2.JOB = preds.pred_job
        )
    )
    -- 避免重复处理同一作业
    AND NOT EXISTS (
        SELECT 1 FROM topology t2 
        WHERE t2.SCHEDULE = mp.SCHEDULE 
        AND t2.JOB = mp.JOB
    )
)
-- 按调度和执行层级排序输出
SELECT SCHEDULE, JOB, PREDECESSORS
FROM topology
ORDER BY SCHEDULE, execution_level, JOB;

关键说明 #

• execution_level标识作业的执行层级，无前置作业层级为1，依赖作业层级依次递增，保证依赖顺序正确。
• 若使用Oracle 12c及以上版本，可用JSON_TABLE更简洁地拆分前置列表：

  SELECT pred_job
  FROM JSON_TABLE(
      '["' || REPLACE(mp.PREDECESSORS, ',', '","') || '"]',
      '$[*]' COLUMNS pred_job VARCHAR2(100) PATH '$'
  )
  

示例输出 #

针对你描述的测试数据，最终输出如下：

（A和D属于同一层级，输出顺序由ORDER BY JOB控制，可根据需求调整）

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