SQL查询实现4级资源父子关系展示及列对齐修正求助

针对你的需求，使用**递归CTE（公共表表达式）**可以准确获取资源的层级路径，避免错位、重复或标识符错误问题。以下是具体实现：

WITH resource_hierarchy AS (
    -- 顶层资源（层级1）
    SELECT 
        res_id,
        description,
        parent_id,
        1 AS level
    FROM resources
    WHERE parent_id IS NULL
    UNION ALL
    -- 递归遍历子资源，层级递增
    SELECT 
        r.res_id,
        r.description,
        r.parent_id,
        rh.level + 1 AS level
    FROM resources r
    JOIN resource_hierarchy rh ON r.parent_id = rh.res_id
),
resource_paths AS (
    -- 按层级提取对应描述，不足4级自动填充null
    SELECT 
        res_id,
        MAX(CASE WHEN level = 1 THEN description END) AS resource_group,
        MAX(CASE WHEN level = 2 THEN description END) AS resource1,
        MAX(CASE WHEN level = 3 THEN description END) AS resource2,
        MAX(CASE WHEN level = 4 THEN description END) AS resource3
    FROM resource_hierarchy
    GROUP BY res_id
)
-- 关联用户与资源数据，输出最终结果
SELECT 
    u.user_id,
    u.username,
    rp.resource_group,
    rp.resource1,
    rp.resource2,
    rp.resource3
FROM users u
JOIN user_resource ur ON u.user_id = ur.user_id
JOIN resource_paths rp ON ur.res_id = rp.res_id
ORDER BY u.user_id;

关键说明： #

1. 递归CTE遍历层级：resource_hierarchy 从顶层资源开始，递归遍历所有子资源，为每个资源标记准确的层级（1到4级），彻底解决层级错位问题。
2. 条件聚合整理列：resource_paths 用CASE语句按层级匹配描述，MAX函数确保每个资源只保留对应层级的有效数据，不足4级的列自动填充null，符合你要求的列对齐格式。
3. 关联表获取用户数据：最后通过JOIN关联用户表、用户-资源关联表和整理好的资源路径表，得到完整的用户-资源层级数据。

如果你的数据库不支持递归CTE（如某些旧版MySQL），可以改用多次自连接的方式：

SELECT 
    u.user_id,
    u.username,
    r1.description AS resource_group,
    r2.description AS resource1,
    r3.description AS resource2,
    r4.description AS resource3
FROM users u
JOIN user_resource ur ON u.user_id = ur.user_id
-- 自连接获取各级资源
LEFT JOIN resources r4 ON ur.res_id = r4.res_id
LEFT JOIN resources r3 ON r4.parent_id = r3.res_id
LEFT JOIN resources r2 ON r3.parent_id = r2.res_id
LEFT JOIN resources r1 ON r2.parent_id = r1.res_id
ORDER BY u.user_id;

自连接方式说明： #

• 从用户关联的资源（r4，最底层）开始，逐层向上关联父资源r3、r2、r1
• 用LEFT JOIN确保层级不足时对应列填充null，避免数据丢失

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