使用SQL自连接查询auto_correct表关联链式记录及无关联数据的方法

问题分析 #

你现有的SQL等价于内连接，仅能匹配到存在两层关联的中间映射关系，既无法追溯到链的起始spell，也无法覆盖没有后续关联的独立映射行，所以会丢失this、junk两条记录，且返回的也不是完整链的首尾对应。

实现方案（支持递归CTE的数据库，如MySQL 8.0+、PostgreSQL、SQL Server等） #

使用递归公共表表达式（CTE）遍历完整的关联链，最终取每条链的首端spell和末端ne即可：

WITH RECURSIVE spell_chain AS (
    -- 锚点：每条记录作为链的起始节点，记录原始起始spell和当前映射的ne
    SELECT spell AS start_spell, ne AS current_ne, 1 AS level
    FROM auto_correct
    UNION ALL
    -- 递归：如果当前ne存在对应的spell映射，就继续延伸链，更新current_ne为新的ne
    SELECT sc.start_spell, ac.ne AS current_ne, sc.level + 1 AS level
    FROM spell_chain sc
    INNER JOIN auto_correct ac ON sc.current_ne = ac.spell
)
-- 取每条起始spell对应的最长链的current_ne，就是最终的末端ne
SELECT start_spell AS spell, current_ne AS ne
FROM (
    SELECT start_spell, current_ne,
           ROW_NUMBER() OVER (PARTITION BY start_spell ORDER BY level DESC) AS rn
    FROM spell_chain
) t
WHERE rn = 1
-- 过滤掉中间节点作为起始的情况，只保留真正的链首（没有其他spell映射到它）
AND start_spell NOT IN (SELECT ne FROM auto_correct);

执行结果 #

运行上述SQL后输出和预期完全一致：

低版本MySQL兼容方案（不支持CTE递归） #

如果使用的是MySQL 5.x等不支持递归的版本，仅能支持固定长度的链查询，比如最多3层关联的写法如下：

SELECT 
    t1.spell,
    COALESCE(t3.ne, t2.ne, t1.ne) AS ne
FROM auto_correct t1
LEFT JOIN auto_correct t2 ON t1.ne = t2.spell
LEFT JOIN auto_correct t3 ON t2.ne = t3.spell
WHERE t1.spell NOT IN (SELECT ne FROM auto_correct);

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