如何在MySQL中实现动态链式查询获取最终链节点？

现有Table_A表数据如下：

需求：基于该表获取每条数据的最终链式节点，规则如下：

• 通过col2匹配col1进行链式查询，直到找不到匹配项为止
• 忽略col1与col2值相同的数据（例如col1=122、col2=881的链式应为122>881>113>765>910>345）
• 无链式或值相同的数据返回NULL

此前尝试自连接仅支持单次链式查询，无法动态处理任意长度的链式，尝试的自连接代码片段：

SELECT A.col1,
       A.col2,
       B.col1
FROM Table_A AS A
LEFT JOIN Table_A AS B

期望结果如下：

请问在MySQL中如何实现该需求？

可以使用**递归CTE（公共表表达式）**来动态处理任意长度的链式查询，这是MySQL 8.0及以上版本支持的特性，具体实现代码如下：

WITH RECURSIVE chain AS (
    -- 基础查询：初始化链式起点，过滤自相等的行
    SELECT 
        col1 AS start_col1,
        col2 AS current_col2,
        col2 AS next_col1,
        1 AS depth
    FROM Table_A
    WHERE col1 != col2

    UNION ALL

    -- 递归查询：延伸链式，直到无法匹配下一个节点
    SELECT 
        c.start_col1,
        t.col2 AS current_col2,
        t.col2 AS next_col1,
        c.depth + 1 AS depth
    FROM chain c
    JOIN Table_A t ON c.next_col1 = t.col1
    WHERE t.col1 != t.col2 -- 跳过自相等的节点，终止无效链式
)
-- 主查询：匹配原始表，提取最终链式节点
SELECT 
    t.col1,
    t.col2,
    CASE 
        WHEN t.col1 = t.col2 THEN NULL
        -- 找到当前链式中无法继续匹配的最后一个节点
        ELSE MAX(CASE WHEN c.next_col1 NOT IN (SELECT col1 FROM Table_A) THEN c.current_col2 END)
    END AS last_chain_data
FROM Table_A t
LEFT JOIN chain c ON t.col1 = c.start_col1
GROUP BY t.col1, t.col2
ORDER BY t.col1;

代码说明 #

1. 递归CTE模块
   • 基础查询：筛选出col1≠col2的行作为链式起点，记录起始节点、当前节点和链式深度。
   • 递归查询：用当前节点的col2匹配下一个节点的col1，持续延伸链式，直到找不到匹配项或遇到自相等节点时停止。
2. 主查询模块
   • 关联原始表与递归结果，对自相等的行直接返回NULL。
   • 对有链式的行，定位到链式中最后一个无法继续匹配的节点，作为最终结果。

执行上述代码后，将得到与期望完全一致的结果，可动态适配任意长度的链式关系，无需手动多层自连接。

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