如何编写SQL语句生成层级路径并更新Table_two的path列？

要实现为每个节点生成包含所有父节点的层级路径并更新到Table_two，可以通过递归查询（CTE）实现，以下是主流数据库的具体写法：

1. 支持递归CTE的数据库（MySQL 8.0+/PostgreSQL/SQL Server） #

WITH RECURSIVE node_paths AS (
    -- 锚点查询：选取所有顶级节点（根据实际数据调整父节点判断条件，比如parent为NULL或0）
    SELECT 
        id,
        name,
        parent,
        CONCAT(name, '/') AS path
    FROM Table_one
    WHERE parent IS NULL OR parent = 0

    UNION ALL

    -- 递归查询：拼接子节点与父节点的路径
    SELECT 
        child.id,
        child.name,
        child.parent,
        CONCAT(parent_path.path, child.name, '/') AS path
    FROM Table_one child
    INNER JOIN node_paths parent_path ON child.parent = parent_path.id
)
-- 关联更新Table_two的path字段
UPDATE Table_two t2
INNER JOIN node_paths np ON t2.id = np.id
SET t2.path = np.path;

2. MySQL 5.x兼容方案（无递归CTE支持） #

如果使用不支持递归CTE的旧版MySQL，可通过自定义函数实现路径拼接：

-- 创建生成路径的函数
DELIMITER //
CREATE FUNCTION get_full_path(node_id INT) RETURNS VARCHAR(500)
DETERMINISTIC
BEGIN
    DECLARE full_path VARCHAR(500) DEFAULT '';
    DECLARE current_id INT DEFAULT node_id;
    DECLARE current_name VARCHAR(100);

    WHILE current_id IS NOT NULL DO
        SELECT name, parent INTO current_name, current_id FROM Table_one WHERE id = current_id;
        SET full_path = CONCAT(current_name, '/', full_path);
    END WHILE;

    RETURN full_path;
END //
DELIMITER ;

-- 执行更新
UPDATE Table_two SET path = get_full_path(id);

关键注意事项 #

• 顶级节点判断：需根据Table_one实际的根节点标识调整WHERE条件，比如部分表用0、部分用NULL表示根节点的父节点。
• 路径格式调整：若不需要末尾的斜杠，删除CONCAT中的'/'即可；若需要开头带斜杠，修改为CONCAT('/', name)。
• 数据一致性：确保Table_one和Table_two的id字段一一对应，否则会有部分记录无法更新。

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