如何通过递归查询获取DEPENDENCIES表的全层级依赖关系？

问题背景 #

我有一个名为DEPENDENCIES的表，结构如下：

CREATE TABLE [LOG].[DEPENDENCIES]
(
    [DEPENDENCY_ID] [int] IDENTITY(1,1) NOT NULL,
    [PROCESS_ID_PARENT] [int] NOT NULL,
    [PROCESS_ID_CHILD] [int] NOT NULL
)

其中Process_Id_Child依赖于Process_Id_Parent，部分依赖存在多层级，但目前我仅能通过以下查询获取到二级依赖：

SELECT
    PAR_PACK.PROCESS_ID AS PARENT_PROCESS_ID,
    PAR_PACK.PACKAGE_NAME AS PARENT_PACKAGE_NAME, 
    P.PROCESS_ID,  
    P.PACKAGE_NAME,  
    CHI_PACK.PROCESS_ID AS CHILD_PROCESS_ID, 
    CHI_PACK.PACKAGE_NAME AS CHILD_PACKAGE_NAME
FROM
    PROCESSES P 
LEFT JOIN 
    DEPENDENCIES PAR ON P.PROCESS_ID = PAR.PROCESS_ID_CHILD 
LEFT JOIN 
    PROCESSES PAR_PACK ON PAR.PROCESS_ID_PARENT = PAR_PACK.PROCESS_ID 
LEFT JOIN 
    DEPENDENCIES CHI ON P.PROCESS_ID = CHI.PROCESS_ID_PARENT 
LEFT JOIN 
    PROCESSES CHI_PACK ON CHI.PROCESS_ID_CHILD = CHI_PACK.PROCESS_ID

PROCESSES表用于获取执行流程的名称，Process ID也存在于该表中。请问获取所有层级依赖的最佳方法是什么？

解决方案：使用递归CTE（公共表表达式） #

针对SQL Server环境，递归CTE是处理任意深度层级依赖的标准方案，以下分场景给出具体实现：

1. 查询所有上游（父级及以上）依赖 #

该查询会遍历每个流程的所有父级、祖父级等上游依赖：

WITH RecursiveParentDependencies AS (
    -- 锚点成员：获取直接父级依赖
    SELECT
        P.PROCESS_ID AS CurrentProcessID,
        P.PACKAGE_NAME AS CurrentProcessName,
        PAR_PACK.PROCESS_ID AS ParentProcessID,
        PAR_PACK.PACKAGE_NAME AS ParentProcessName,
        1 AS DependencyLevel -- 标记层级，1为直接父级
    FROM PROCESSES P
    JOIN DEPENDENCIES PAR ON P.PROCESS_ID = PAR.PROCESS_ID_CHILD
    JOIN PROCESSES PAR_PACK ON PAR.PROCESS_ID_PARENT = PAR_PACK.PROCESS_ID

    UNION ALL

    -- 递归成员：向上遍历父级的父级
    SELECT
        RPD.CurrentProcessID,
        RPD.CurrentProcessName,
        PAR_PACK.PROCESS_ID AS ParentProcessID,
        PAR_PACK.PACKAGE_NAME AS ParentProcessName,
        RPD.DependencyLevel + 1 AS DependencyLevel
    FROM RecursiveParentDependencies RPD
    JOIN DEPENDENCIES PAR ON RPD.ParentProcessID = PAR.PROCESS_ID_CHILD
    JOIN PROCESSES PAR_PACK ON PAR.PROCESS_ID_PARENT = PAR_PACK.PROCESS_ID
)
SELECT * FROM RecursiveParentDependencies
ORDER BY CurrentProcessID, DependencyLevel;

2. 查询所有下游（子级及以下）依赖 #

该查询会遍历每个流程的所有子级、孙级等下游依赖：

WITH RecursiveChildDependencies AS (
    -- 锚点成员：获取直接子级依赖
    SELECT
        P.PROCESS_ID AS CurrentProcessID,
        P.PACKAGE_NAME AS CurrentProcessName,
        CHI_PACK.PROCESS_ID AS ChildProcessID,
        CHI_PACK.PACKAGE_NAME AS ChildProcessName,
        1 AS DependencyLevel -- 标记层级，1为直接子级
    FROM PROCESSES P
    JOIN DEPENDENCIES CHI ON P.PROCESS_ID = CHI.PROCESS_ID_PARENT
    JOIN PROCESSES CHI_PACK ON CHI.PROCESS_ID_CHILD = CHI_PACK.PROCESS_ID

    UNION ALL

    -- 递归成员：向下遍历子级的子级
    SELECT
        RCD.CurrentProcessID,
        RCD.CurrentProcessName,
        CHI_PACK.PROCESS_ID AS ChildProcessID,
        CHI_PACK.PACKAGE_NAME AS ChildProcessName,
        RCD.DependencyLevel + 1 AS DependencyLevel
    FROM RecursiveChildDependencies RCD
    JOIN DEPENDENCIES CHI ON RCD.ChildProcessID = CHI.PROCESS_ID_PARENT
    JOIN PROCESSES CHI_PACK ON CHI.PROCESS_ID_CHILD = CHI_PACK.PROCESS_ID
)
SELECT * FROM RecursiveChildDependencies
ORDER BY CurrentProcessID, DependencyLevel;

3. 同时获取上下游全层级依赖 #

如果需要一次性查看每个流程的所有上下游依赖，可以合并两个递归CTE的结果：

WITH ParentCTE AS (
    -- 上游递归逻辑
    SELECT
        P.PROCESS_ID AS ProcessID,
        P.PACKAGE_NAME AS ProcessName,
        PAR_PACK.PROCESS_ID AS RelatedProcessID,
        PAR_PACK.PACKAGE_NAME AS RelatedProcessName,
        'Parent' AS DependencyType,
        1 AS Level
    FROM PROCESSES P
    JOIN DEPENDENCIES PAR ON P.PROCESS_ID = PAR.PROCESS_ID_CHILD
    JOIN PROCESSES PAR_PACK ON PAR.PROCESS_ID_PARENT = PAR_PACK.PROCESS_ID
    UNION ALL
    SELECT
        PC.ProcessID,
        PC.ProcessName,
        PAR_PACK.PROCESS_ID AS RelatedProcessID,
        PAR_PACK.PACKAGE_NAME AS RelatedProcessName,
        'Parent' AS DependencyType,
        PC.Level + 1
    FROM ParentCTE PC
    JOIN DEPENDENCIES PAR ON PC.RelatedProcessID = PAR.PROCESS_ID_CHILD
    JOIN PROCESSES PAR_PACK ON PAR.PROCESS_ID_PARENT = PAR_PACK.PROCESS_ID
),
ChildCTE AS (
    -- 下游递归逻辑
    SELECT
        P.PROCESS_ID AS ProcessID,
        P.PACKAGE_NAME AS ProcessName,
        CHI_PACK.PROCESS_ID AS RelatedProcessID,
        CHI_PACK.PACKAGE_NAME AS RelatedProcessName,
        'Child' AS DependencyType,
        1 AS Level
    FROM PROCESSES P
    JOIN DEPENDENCIES CHI ON P.PROCESS_ID = CHI.PROCESS_ID_PARENT
    JOIN PROCESSES CHI_PACK ON CHI.PROCESS_ID_CHILD = CHI_PACK.PROCESS_ID
    UNION ALL
    SELECT
        CC.ProcessID,
        CC.ProcessName,
        CHI_PACK.PROCESS_ID AS RelatedProcessID,
        CHI_PACK.PACKAGE_NAME AS RelatedProcessName,
        'Child' AS DependencyType,
        CC.Level + 1
    FROM ChildCTE CC
    JOIN DEPENDENCIES CHI ON CC.RelatedProcessID = CHI.PROCESS_ID_PARENT
    JOIN PROCESSES CHI_PACK ON CHI.PROCESS_ID_CHILD = CHI_PACK.PROCESS_ID
)
SELECT * FROM ParentCTE
UNION ALL
SELECT * FROM ChildCTE
ORDER BY ProcessID, DependencyType, Level;

关键注意事项 #

• 避免循环依赖：如果DEPENDENCIES表存在循环依赖（如A依赖B，B又依赖A），递归会陷入死循环，需在递归成员中添加条件过滤已访问的节点。
• 调整递归层级：SQL Server默认递归上限为100层，若依赖层级超过此限制，可在查询末尾添加OPTION (MAXRECURSION n)，n为指定最大层级，或用0表示无限制（不推荐无限制）。
• 灵活过滤结果：可根据需求修改JOIN为LEFT JOIN，并调整WHERE条件，以包含无依赖的流程。

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