Postgres递归查询获取子节点所有祖先并关联其他表补充信息的技术咨询

嘿，作为后端新手，递归CTE确实一开始会有点绕，不过咱们一步步拆解，帮你搞定这个获取层级祖先+补充成本的需求！

首先，先明确你要的核心：从某个子节点往上（或者从顶层往下）拉取完整的用户层级链，同时带上每个用户的成本，用来做利润分配对吧？咱们先从基础的递归CTE逻辑讲起，再结合你的需求写实际SQL。

先搞懂递归CTE的基本结构 #

递归CTE分两部分，用UNION ALL连起来：

1. 锚点成员：就是查询的“起点”——比如顶层的用户（没有上级的那个，master_id为null），或者你指定的某个子节点。
2. 递归成员：就是循环执行的逻辑——用已经查到的结果，去关联原表找下一层（或上一层）的节点，直到没有新节点为止。

结合你的需求写实际代码 #

假设你有两张表：

• 一张存用户的层级关联（比如product_user_hierarchy，字段product_id、user_id、parent_user_id——parent_user_id就是你要的master_id）
• 一张存每个用户的成本（比如user_cost_details，字段user_id、cost）

先插点示例数据对应你要的结果：

-- 创建层级表
CREATE TABLE product_user_hierarchy (
    product_id INT,
    user_id VARCHAR(50),
    parent_user_id VARCHAR(50)
);

-- 创建成本表
CREATE TABLE user_cost_details (
    user_id VARCHAR(50),
    cost INT
);

-- 插入示例数据
INSERT INTO product_user_hierarchy VALUES
(1, 'john_snow', NULL),
(1, 'bran_stark', 'john_snow'),
(1, 'arya_stark', 'bran_stark'),
(1, 'sansa_stark', 'arya_stark');

INSERT INTO user_cost_details VALUES
('john_snow', 4),
('bran_stark', 6),
('arya_stark', 8),
('sansa_stark', 10);

场景1：从顶层往下拉取完整层级（得到你要的结果格式）

用递归CTE从顶层节点（john_snow）开始，逐层往下找子节点，同时关联成本表拿到cost：

WITH RECURSIVE user_hierarchy AS (
    -- 锚点成员：先拿顶层用户（没有上级的）
    SELECT 
        u.user_id,
        NULL AS master_id,
        uc.cost
    FROM product_user_hierarchy u
    JOIN user_cost_details uc ON u.user_id = uc.user_id
    WHERE u.parent_user_id IS NULL 
      AND u.product_id = 1 -- 可以指定某个产品，去掉就查所有产品的层级
    
    UNION ALL
    
    -- 递归成员：循环找子节点，把父节点的user_id作为当前节点的master_id
    SELECT 
        child.user_id,
        parent.user_id AS master_id,
        uc.cost
    FROM product_user_hierarchy child
    -- 用当前已找到的父节点，关联子节点的parent_user_id
    JOIN user_hierarchy parent ON child.parent_user_id = parent.user_id
    JOIN user_cost_details uc ON child.user_id = uc.user_id
    WHERE child.product_id = 1
)
SELECT * FROM user_hierarchy;

运行这个查询，就能得到你想要的结果：

场景2：从某个子节点往上拉取所有祖先（比如查sansa的所有上级）

如果你需要从某个子节点（比如sansa_stark）出发，拿到它的所有祖先，只需要调整锚点和递归的方向：

WITH RECURSIVE user_ancestors AS (
    -- 锚点成员：从目标子节点开始
    SELECT 
        u.user_id,
        u.parent_user_id AS master_id,
        uc.cost
    FROM product_user_hierarchy u
    JOIN user_cost_details uc ON u.user_id = uc.user_id
    WHERE u.user_id = 'sansa_stark'
    
    UNION ALL
    
    -- 递归成员：往上找父节点，把父节点的parent_user_id作为它的master_id
    SELECT 
        parent.user_id,
        parent.parent_user_id AS master_id,
        uc.cost
    FROM product_user_hierarchy parent
    -- 用当前节点的master_id，关联父节点的user_id
    JOIN user_ancestors child ON parent.user_id = child.master_id
    JOIN user_cost_details uc ON parent.user_id = uc.user_id
)
SELECT * FROM user_ancestors;

这个查询会返回从sansa到john的所有层级，刚好满足你“获取某个子节点的所有关联祖先信息”的需求。

给新手的小提醒 #

• 递归CTE必须加RECURSIVE关键字（PostgreSQL、MySQL 8+、SQL Server都支持，不同数据库语法略有差异）
• 锚点和递归成员的字段数量、数据类型必须完全一致，不然会报错
• 要避免无限递归：如果你的层级表有循环引用（比如A的上级是B，B的上级是A），一定要在递归成员里加条件终止循环
• 针对利润分配：你可以用这个结果计算每个用户的利润（比如当前用户cost减去上级cost的差值），而顶层的最低cost（4）直接计入公司利润，逻辑就很清晰啦！

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