使用连接与GROUP_CONCAT处理主表及查找表的递归父级

嘿，恭喜你已经搞定了主表和多LUT基础拼接的问题！现在遇到多级父级的情况，确实靠暴力多表JOIN会越写越臃肿，维护起来也麻烦，这里给你两个更优雅的解决方案，适配不同的场景：

这是MySQL 8.0及以上版本最推荐的方式，递归CTE可以自动遍历整个层级结构，把每个节点的完整父级路径（从顶级到当前节点）拼接出来，不用手动写N次JOIN。

举个实际的代码例子，假设你的查找表叫lookup_table，结构是id, name, parent_id，主表main_table里有lut_id关联到查找表的id：

-- 递归CTE生成每个节点的完整层级路径
WITH RECURSIVE lut_hierarchy AS (
    -- 先抓顶级节点（parent_id为NULL或0，根据你的实际情况调整）
    SELECT 
        id,
        name,
        parent_id,
        CAST(name AS CHAR(255)) AS full_path
    FROM lookup_table
    WHERE parent_id IS NULL OR parent_id = 0

    UNION ALL

    -- 递归遍历子节点，拼接路径
    SELECT 
        child.id,
        child.name,
        child.parent_id,
        CONCAT(parent.full_path, ' > ', child.name) AS full_path
    FROM lookup_table child
    JOIN lut_hierarchy parent ON child.parent_id = parent.id
)
-- 关联主表，把路径追加到Description后面
SELECT 
    m.id,
    CONCAT(m.Description, ' | 层级路径: ', COALESCE(l.full_path, '无关联')) AS extended_description
FROM main_table m
LEFT JOIN lut_hierarchy l ON m.lut_id = l.id;

这个方法的好处是逻辑清晰，一次查询就能搞定所有层级，而且性能比多次JOIN要好很多，尤其是当层级比较深的时候。

如果你的MySQL版本低于8.0（不支持递归CTE），或者需要在多个查询里重复使用这个层级拼接的逻辑，可以写一个自定义函数，输入节点ID就能返回完整路径：

DELIMITER //
CREATE FUNCTION get_lut_full_path(node_id INT) 
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    DECLARE path VARCHAR(255);
    DECLARE current_id INT;
    DECLARE current_name VARCHAR(100);

    SET path = '';
    SET current_id = node_id;

    -- 循环向上找父级，直到顶级节点
    WHILE current_id IS NOT NULL DO
        SELECT name, parent_id INTO current_name, current_id FROM lookup_table WHERE id = current_id;
        SET path = CONCAT(current_name, IF(path = '', '', ' > '), path);
    END WHILE;

    RETURN path;
END //
DELIMITER ;

-- 调用函数拼接Description
SELECT 
    id,
    CONCAT(Description, ' | 层级路径: ', COALESCE(get_lut_full_path(lut_id), '无关联')) AS extended_description
FROM main_table;

用函数的话，查询主表的代码会非常简洁，而且函数可以随时修改逻辑，不用改每个查询。

• 如果你的层级路径可能很长，记得调整VARCHAR的长度，避免内容被截断
• 用COALESCE处理没有关联查找表的记录，防止出现NULL破坏拼接结果
• 如果是MySQL 8.0+，优先用递归CTE，性能和可读性都更优

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