多层级关联下同表数据获取：用户层级树数据库实现问询

针对你这个多父多子的用户层级树场景，我来梳理下怎么用SQL从user和position表中获取需要的层级数据。首先明确下核心逻辑：position表是层级关联的核心，一个用户可以对应多个职位，职位之间通过parent_id形成上下级链，用户的层级关系正是通过这些职位关联来实现的。下面是几种常见场景的具体查询方案：

1. 获取指定用户的所有上级（递归向上遍历） #

比如要查询用户Harry Potter（user.id=14）的所有上级，我们可以用递归CTE（你的MariaDB 10.2版本已经支持）来遍历职位的父节点链：

WITH RECURSIVE user_hierarchy AS (
    -- 起始节点：目标用户的所有职位
    SELECT 
        p.id AS position_id,
        p.parent_id,
        p.user_id,
        u.first_name,
        u.last_name,
        p.type,
        1 AS level
    FROM position p
    JOIN user u ON p.user_id = u.id
    WHERE p.user_id = 14
    UNION ALL
    -- 递归向上查询父职位对应的用户
    SELECT 
        p.id AS position_id,
        p.parent_id,
        p.user_id,
        u.first_name,
        u.last_name,
        p.type,
        uh.level + 1 AS level
    FROM position p
    JOIN user u ON p.user_id = u.id
    JOIN user_hierarchy uh ON p.id = uh.parent_id
)
SELECT DISTINCT -- 去重：同一个上级可能通过多条职位链关联到目标用户
    level,
    first_name,
    last_name,
    type
FROM user_hierarchy
WHERE parent_id IS NOT NULL -- 可选：排除最顶层无父节点的用户
ORDER BY level DESC; -- 从最高层级到最低层级排序

2. 获取指定用户的所有下级（递归向下遍历） #

比如要查询Albus Dumbledore（user.id=3）的所有下级，递归方向改为向下遍历子职位：

WITH RECURSIVE user_hierarchy AS (
    -- 起始节点：目标用户的所有职位
    SELECT 
        p.id AS position_id,
        p.parent_id,
        p.user_id,
        u.first_name,
        u.last_name,
        p.type,
        1 AS level
    FROM position p
    JOIN user u ON p.user_id = u.id
    WHERE p.user_id = 3
    UNION ALL
    -- 递归向下查询子职位对应的用户
    SELECT 
        p.id AS position_id,
        p.parent_id,
        p.user_id,
        u.first_name,
        u.last_name,
        p.type,
        uh.level + 1 AS level
    FROM position p
    JOIN user u ON p.user_id = u.id
    JOIN user_hierarchy uh ON p.parent_id = uh.position_id
)
SELECT DISTINCT
    level,
    first_name,
    last_name,
    type
FROM user_hierarchy
WHERE user_id != 3 -- 排除目标用户自身
ORDER BY level ASC; -- 从直接下级到最底层排序

3. 生成完整的组织层级树 #

如果需要输出整个组织的层级结构，包含每个节点的路径，可以从所有顶级职位（parent_id IS NULL）开始递归：

WITH RECURSIVE full_hierarchy AS (
    -- 顶级节点：所有无父职位的职位
    SELECT 
        p.id AS position_id,
        p.parent_id,
        p.user_id,
        u.first_name,
        u.last_name,
        p.type,
        CONCAT(u.first_name, ' ', u.last_name) AS path,
        1 AS level
    FROM position p
    JOIN user u ON p.user_id = u.id
    WHERE p.parent_id IS NULL
    UNION ALL
    -- 递归遍历子节点
    SELECT 
        p.id AS position_id,
        p.parent_id,
        p.user_id,
        u.first_name,
        u.last_name,
        p.type,
        CONCAT(fh.path, ' > ', u.first_name, ' ', u.last_name) AS path,
        fh.level + 1 AS level
    FROM position p
    JOIN user u ON p.user_id = u.id
    JOIN full_hierarchy fh ON p.parent_id = fh.position_id
)
SELECT
    level,
    path,
    type
FROM full_hierarchy
ORDER BY level, path;

这个查询会生成类似Albus Dumbledore > Severus Rogue > Pomona Chourave的路径，清晰展示每个节点的层级归属。

关键注意事项 #

• 去重处理：因为一个用户可能对应多个职位，递归查询后必须用DISTINCT避免同一个用户重复出现在结果中
• 职位类型过滤：可以在查询中添加WHERE p.type = 'TYPE_HR_PLUS'这类条件，只筛选特定类型的职位层级
• 兼容低版本数据库：如果你的数据库不支持CTE（比如MySQL < 8.0），可以用存储过程或多层自连接实现，但CTE是最简洁易维护的方式

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