跨表递归查询开发：A、B表层级动作链查询需求

一、数据表结构 #

TABLE A（仅作为动作链起始节点）：

A_ID -- 主键，唯一标识A节点

TABLE B（可作为起始节点，或关联A/B节点）：

B_ID          -- 主键，唯一标识B节点
LINKS_TO_A_ID -- 关联TABLE A的A_ID（可为空）
LINKS_TO_B_ID -- 关联TABLE B的B_ID（可为空）

注：B节点不能关联A节点（仅A可作为起始），链中出现A节点后只能向下关联B节点

二、层级规则说明 #

合法层级结构示例 #

A->B->B->B->B  -- 以A为起始，后续仅关联B节点
B->B->B        -- 以独立B为起始，后续仅关联B节点

非法层级结构示例 #

B->A->B  -- B节点不能关联A节点，更不能在A后继续延伸
B->A     -- B节点禁止关联A节点

三、需求说明 #

需构建递归查询实现：

• 获取所有以A为起始的完整动作链（A→B→B...）
• 获取所有以独立B为起始的完整动作链（B→B→B...）
• 列出层级内所有关联节点
• 兼容TABLE B同一行被多次引用的场景（如同一B节点被多个B节点关联）

四、示例关联数据 #

RowA1 -- TABLE A的起始节点
RowB1_referencingA1 -- TABLE B节点，关联RowA1
RowB2_referencingB1 -- TABLE B节点，关联RowB1
RowB3 -- TABLE B的独立起始节点（无关联）
RowB4_referencingB3 -- TABLE B节点，关联RowB3
RowB5_referencingB3 -- TABLE B节点，关联RowB3
RowB6_referencingB5 -- TABLE B节点，关联RowB5

五、递归查询实现（适配MySQL 8+/PostgreSQL） #

1. 查询以A为起始的动作链 #

通过递归CTE从A节点出发，逐层关联合法B节点：

WITH RECURSIVE a_chain AS (
    -- 锚点：TABLE A所有起始节点
    SELECT 
        A_ID AS node_id,
        'A' AS node_type,
        CAST(A_ID AS CHAR) AS chain_path,
        1 AS level
    FROM TABLE_A

    UNION ALL

    -- 递归：关联A节点直接指向的B节点
    SELECT 
        b.B_ID AS node_id,
        'B' AS node_type,
        CONCAT(ac.chain_path, '->', b.B_ID) AS chain_path,
        ac.level + 1 AS level
    FROM a_chain ac
    JOIN TABLE_B b ON ac.node_id = b.LINKS_TO_A_ID
    WHERE b.LINKS_TO_B_ID IS NULL

    UNION ALL

    -- 递归：关联B节点的下一级B节点
    SELECT 
        b_next.B_ID AS node_id,
        'B' AS node_type,
        CONCAT(ac.chain_path, '->', b_next.B_ID) AS chain_path,
        ac.level + 1 AS level
    FROM a_chain ac
    JOIN TABLE_B b_next ON ac.node_id = b_next.LINKS_TO_B_ID
    WHERE ac.node_type = 'B'
      AND b_next.LINKS_TO_A_ID IS NULL
)
SELECT chain_path, level, node_id, node_type
FROM a_chain
ORDER BY chain_path, level;

2. 查询以独立B为起始的动作链 #

从无关联的B节点出发，逐层关联后续合法B节点：

WITH RECURSIVE b_chain AS (
    -- 锚点：TABLE B中无关联的独立起始节点
    SELECT 
        B_ID AS node_id,
        'B' AS node_type,
        CAST(B_ID AS CHAR) AS chain_path,
        1 AS level
    FROM TABLE_B
    WHERE LINKS_TO_A_ID IS NULL 
      AND LINKS_TO_B_ID IS NULL

    UNION ALL

    -- 递归：关联下一级B节点
    SELECT 
        b_next.B_ID AS node_id,
        'B' AS node_type,
        CONCAT(bc.chain_path, '->', b_next.B_ID) AS chain_path,
        bc.level + 1 AS level
    FROM b_chain bc
    JOIN TABLE_B b_next ON bc.node_id = b_next.LINKS_TO_B_ID
    WHERE b_next.LINKS_TO_A_ID IS NULL
)
SELECT chain_path, level, node_id, node_type
FROM b_chain
ORDER BY chain_path, level;

3. 合并两类结果（可选） #

若需一次性获取所有合法动作链，可合并两个CTE的结果：

WITH RECURSIVE a_chain AS (
    SELECT 
        A_ID AS node_id,
        'A' AS node_type,
        CAST(A_ID AS CHAR) AS chain_path,
        1 AS level
    FROM TABLE_A

    UNION ALL

    SELECT 
        b.B_ID AS node_id,
        'B' AS node_type,
        CONCAT(ac.chain_path, '->', b.B_ID) AS chain_path,
        ac.level + 1 AS level
    FROM a_chain ac
    JOIN TABLE_B b ON ac.node_id = b.LINKS_TO_A_ID
    WHERE b.LINKS_TO_B_ID IS NULL

    UNION ALL

    SELECT 
        b_next.B_ID AS node_id,
        'B' AS node_type,
        CONCAT(ac.chain_path, '->', b_next.B_ID) AS chain_path,
        ac.level + 1 AS level
    FROM a_chain ac
    JOIN TABLE_B b_next ON ac.node_id = b_next.LINKS_TO_B_ID
    WHERE ac.node_type = 'B'
      AND b_next.LINKS_TO_A_ID IS NULL
),
b_chain AS (
    SELECT 
        B_ID AS node_id,
        'B' AS node_type,
        CAST(B_ID AS CHAR) AS chain_path,
        1 AS level
    FROM TABLE_B
    WHERE LINKS_TO_A_ID IS NULL 
      AND LINKS_TO_B_ID IS NULL

    UNION ALL

    SELECT 
        b_next.B_ID AS node_id,
        'B' AS node_type,
        CONCAT(bc.chain_path, '->', b_next.B_ID) AS chain_path,
        bc.level + 1 AS level
    FROM b_chain bc
    JOIN TABLE_B b_next ON bc.node_id = b_next.LINKS_TO_B_ID
    WHERE b_next.LINKS_TO_A_ID IS NULL
)
SELECT chain_path, level, node_id, node_type FROM a_chain
UNION ALL
SELECT chain_path, level, node_id, node_type FROM b_chain
ORDER BY chain_path, level;

说明：上述查询通过递归CTE逐层遍历节点，通过WHERE条件过滤非法关联，确保生成的链均为合法结构。对于TABLE B中被多次引用的节点（如示例中的RowB3），递归会自动处理多分支，生成所有对应完整链。

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