如何将Oracle的CONNECT_BY系列层级查询特性迁移至SQL Server？

原Oracle查询语句 #

SELECT
    sub.COLUMN_1,
    sub.COLUMN_2,
    sub.COLUMN_3,
    CONNECT_BY_ROOT sub.COLUMN_4,
    CONNECT_BY_ROOT sub.COLUMN_5,
    CONNECT_BY_ROOT sub.COLUMN_6,
    CONNECT_BY_ROOT sub.COLUMN_7,
    CONNECT_BY_ROOT sub.COLUMN_8,
    CONNECT_BY_ROOT sub.COLUMN_9,
    sub.COLUMN_10
FROM
    TABLE sub
WHERE
    1 = CONNECT_BY_ISLEAF
AND
    (
        2 <= LEVEL
    OR
        sub.COLUMN_11 IS NULL
    )
START WITH
    0 = sub.COLUMN_12
CONNECT BY PRIOR
    sub.COLUMN_8 = sub.COLUMN_13
AND
    sub.COLUMN_7 = PRIOR sub.COLUMN_7

核心疑问解答 #

1. 模拟CONNECT_BY_ISLEAF的实现 #

CONNECT_BY_ISLEAF用于识别层级中的叶子节点（无后续子节点的行）。在SQL Server递归CTE中，不需要额外ORDER BY，直接通过NOT EXISTS判断当前节点是否存在符合连接条件的子节点即可：

• 在最终查询的WHERE子句中，关联原表检查是否有满足COLUMN_13 = 当前节点COLUMN_8且COLUMN_7 = 当前节点COLUMN_7的行，不存在则为叶子节点。

2. 处理原WHERE中的AND组合条件 #

原条件分为两部分，直接移植到递归CTE的最终查询WHERE子句即可：

• 叶子节点判断：对应上述NOT EXISTS语句
• 层级/COLUMN_11判断：用递归CTE中自定义的lvl字段替代Oracle的LEVEL，直接写(lvl >= 2 OR COLUMN_11 IS NULL)

完整转换后的SQL Server代码 #

WITH RecursiveCTE AS (
    -- 锚点成员：对应Oracle START WITH
    SELECT
        COLUMN_1,
        COLUMN_2,
        COLUMN_3,
        COLUMN_4 AS ROOT_COLUMN_4,
        COLUMN_5 AS ROOT_COLUMN_5,
        COLUMN_6 AS ROOT_COLUMN_6,
        COLUMN_7 AS ROOT_COLUMN_7,
        COLUMN_8 AS ROOT_COLUMN_8,
        COLUMN_9 AS ROOT_COLUMN_9,
        COLUMN_10,
        COLUMN_11,
        COLUMN_7 AS CURRENT_COLUMN_7,
        COLUMN_8 AS CURRENT_COLUMN_8,
        1 AS lvl -- 自定义层级字段，对应Oracle LEVEL
    FROM TABLE sub
    WHERE sub.COLUMN_12 = 0

    UNION ALL

    -- 递归成员：对应Oracle CONNECT BY
    SELECT
        sub.COLUMN_1,
        sub.COLUMN_2,
        sub.COLUMN_3,
        rcte.ROOT_COLUMN_4, -- 传递根节点值，对应CONNECT_BY_ROOT
        rcte.ROOT_COLUMN_5,
        rcte.ROOT_COLUMN_6,
        rcte.ROOT_COLUMN_7,
        rcte.ROOT_COLUMN_8,
        rcte.ROOT_COLUMN_9,
        sub.COLUMN_10,
        sub.COLUMN_11,
        sub.COLUMN_7 AS CURRENT_COLUMN_7,
        sub.COLUMN_8 AS CURRENT_COLUMN_8,
        rcte.lvl + 1 AS lvl
    FROM TABLE sub
    INNER JOIN RecursiveCTE rcte
        ON rcte.CURRENT_COLUMN_8 = sub.COLUMN_13 -- 对应PRIOR sub.COLUMN_8 = sub.COLUMN_13
        AND sub.COLUMN_7 = rcte.CURRENT_COLUMN_7 -- 对应sub.COLUMN_7 = PRIOR sub.COLUMN_7
)
-- 最终查询：应用原WHERE条件
SELECT
    COLUMN_1,
    COLUMN_2,
    COLUMN_3,
    ROOT_COLUMN_4,
    ROOT_COLUMN_5,
    ROOT_COLUMN_6,
    ROOT_COLUMN_7,
    ROOT_COLUMN_8,
    ROOT_COLUMN_9,
    COLUMN_10
FROM RecursiveCTE
WHERE
    -- 模拟CONNECT_BY_ISLEAF：无匹配子节点
    NOT EXISTS (
        SELECT 1
        FROM TABLE sub
        WHERE sub.COLUMN_13 = RecursiveCTE.CURRENT_COLUMN_8
          AND sub.COLUMN_7 = RecursiveCTE.CURRENT_COLUMN_7
    )
    -- 原AND组合条件
    AND (lvl >= 2 OR COLUMN_11 IS NULL);

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