如何在MySQL中查询用户父子关系及任意两用户的层级步数差？

嘿，我来帮你搞定这个MySQL用户父子层级步数计算的问题！

首先咱们先明确下你的Users表结构，用表格展示更清晰：

假设你的示例数据对应你提到的dave→pritesh→larry→abha路径，大概是这样：

INSERT INTO Users (user_name, parent_user_id) VALUES
('abha', NULL),   -- 根节点
('larry', 1),
('pritesh', 2),
('dave', 3),
('john', 1),
('emily', 5);

核心思路：用递归CTE遍历层级关系 #

MySQL 8.0及以上支持递归CTE（Common Table Expression），这是处理树形层级数据最方便的方式。我们可以通过递归生成每个用户到其所有祖先的路径和步数，再基于这个结果查询任意两用户的步数差。

步骤1：生成所有用户的层级路径与步数 #

先写一个递归CTE，遍历每个用户的所有祖先节点，记录从该用户到祖先的步数和完整路径：

WITH RECURSIVE UserHierarchy AS (
    -- 基础情况：每个用户自身是自己的祖先，步数为0
    SELECT 
        user_id AS descendant_id,
        user_name AS descendant_name,
        user_id AS ancestor_id,
        user_name AS ancestor_name,
        0 AS steps,
        CAST(user_name AS CHAR(255)) AS hierarchy_path
    FROM Users
    
    UNION ALL
    
    -- 递归情况：向上遍历父节点，步数累加1，路径追加父用户名
    SELECT 
        uh.descendant_id,
        uh.descendant_name,
        u.user_id AS ancestor_id,
        u.user_name AS ancestor_name,
        uh.steps + 1,
        CONCAT(uh.hierarchy_path, '→', u.user_name)
    FROM UserHierarchy uh
    JOIN Users u ON uh.ancestor_id = u.parent_user_id
)
SELECT * FROM UserHierarchy;

执行这个查询后，你会看到每条记录对应一个用户（descendant）到其某个祖先（ancestor）的步数和路径，比如dave对应的记录里会有到abha的条目，步数为3，路径是dave→pritesh→larry→abha。

步骤2：查询任意两用户的层级步数差 #

场景1：两用户在同一条直接路径上（比如dave和abha）

直接从上面的CTE中筛选目标用户即可：

WITH RECURSIVE UserHierarchy AS (
    SELECT 
        user_id AS descendant_id,
        user_name AS descendant_name,
        user_id AS ancestor_id,
        user_name AS ancestor_name,
        0 AS steps,
        CAST(user_name AS CHAR(255)) AS hierarchy_path
    FROM Users
    UNION ALL
    SELECT 
        uh.descendant_id,
        uh.descendant_name,
        u.user_id AS ancestor_id,
        u.user_name AS ancestor_name,
        uh.steps + 1,
        CONCAT(uh.hierarchy_path, '→', u.user_name)
    FROM UserHierarchy uh
    JOIN Users u ON uh.ancestor_id = u.parent_user_id
)
SELECT 
    descendant_name AS 用户A,
    ancestor_name AS 用户B,
    steps AS 层级步数差,
    hierarchy_path AS 层级路径
FROM UserHierarchy
WHERE descendant_name = 'dave' AND ancestor_name = 'abha';

执行结果会直接返回：用户A是dave，用户B是abha，步数差3，路径dave→pritesh→larry→abha。

场景2：两用户不在同一条直接路径上（比如dave和emily）

这时候需要先找到他们的最近共同祖先，再计算各自到共同祖先的步数之和：

WITH RECURSIVE UserHierarchy AS (
    SELECT 
        user_id AS descendant_id,
        user_name AS descendant_name,
        user_id AS ancestor_id,
        user_name AS ancestor_name,
        0 AS steps,
        CAST(user_name AS CHAR(255)) AS hierarchy_path
    FROM Users
    UNION ALL
    SELECT 
        uh.descendant_id,
        uh.descendant_name,
        u.user_id AS ancestor_id,
        u.user_name AS ancestor_name,
        uh.steps + 1,
        CONCAT(uh.hierarchy_path, '→', u.user_name)
    FROM UserHierarchy uh
    JOIN Users u ON uh.ancestor_id = u.parent_user_id
)
SELECT 
    uh1.descendant_name AS 用户1,
    uh2.descendant_name AS 用户2,
    uh1.steps + uh2.steps AS 总层级步数差,
    CONCAT(uh1.hierarchy_path, '←', REPLACE(uh2.hierarchy_path, common_ancestor.ancestor_name, '')) AS 完整路径
FROM UserHierarchy uh1
JOIN UserHierarchy uh2 ON uh1.ancestor_id = uh2.ancestor_id
-- 子查询找最近共同祖先
JOIN (
    SELECT 
        uh1.descendant_id,
        uh2.descendant_id,
        uh1.ancestor_id,
        uh1.ancestor_name
    FROM UserHierarchy uh1
    JOIN UserHierarchy uh2 ON uh1.ancestor_id = uh2.ancestor_id
    WHERE uh1.descendant_name = 'dave' AND uh2.descendant_name = 'emily'
    ORDER BY uh1.steps DESC
    LIMIT 1
) common_ancestor ON uh1.descendant_id = common_ancestor.descendant_id 
    AND uh1.ancestor_id = common_ancestor.ancestor_id 
    AND uh2.descendant_id = common_ancestor.descendant_id 
    AND uh2.ancestor_id = common_ancestor.ancestor_id;

这个查询会返回：用户1是dave，用户2是emily，总步数差5，完整路径dave→pritesh→larry→abha←john←emily。

注意事项 #

• 这个方案要求MySQL版本在8.0及以上，因为递归CTE是8.0才引入的特性；
• 如果你的parent_user_id允许为NULL（根节点），递归会自动在根节点停止，不会陷入死循环；
• 如果存在循环引用（比如A的父是B，B的父是A），需要在递归中添加条件避免死循环，比如记录已访问的节点ID。

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