Postgres递归查询问题：如何获取用户的X级上级推荐人？

我来帮你搞定这个Postgres递归查询的问题！你之前用LIMIT的方式确实不够精准，递归CTE（公共表表达式）才是处理这种层级关系的正确打开方式，而且能轻松控制终止深度。

核心思路：用递归CTE跟踪深度，达到指定值立即终止 #

递归CTE分为两部分：锚点成员（起始用户）和递归成员（逐层向上查找推荐人），同时我们会在每一层记录当前深度，当深度达到你设定的X值时，就停止递归。

完整示例查询 #

假设你的用户表名为users，要查询user_id = 123的最多3级上级推荐人（这里X=3）：

WITH RECURSIVE referral_chain AS (
    -- 锚点成员：选中目标用户，初始深度设为0（代表自己）
    SELECT 
        user_id,
        referred_by_user_id,
        0 AS depth
    FROM users
    WHERE user_id = 123 -- 替换成你要查询的目标用户ID
    UNION ALL
    -- 递归成员：向上查找推荐人，深度+1，直到达到设定的最大深度
    SELECT 
        u.user_id,
        u.referred_by_user_id,
        rc.depth + 1 AS depth
    FROM users u
    JOIN referral_chain rc ON u.user_id = rc.referred_by_user_id
    WHERE rc.depth < 3 -- 这里的3就是你要的X深度，替换成需要的数值
)
-- 最终返回完整的推荐链条
SELECT user_id, referred_by_user_id, depth FROM referral_chain;

关键细节解释 #

1. 锚点成员：先定位到你要查询的起始用户，把深度初始化为0。如果不需要包含起始用户，你可以直接从他的第一个推荐人开始，把初始深度设为1：

   -- 仅从第一级上级开始的锚点
   SELECT 
       u.user_id,
       u.referred_by_user_id,
       1 AS depth
   FROM users u
   WHERE u.user_id = (SELECT referred_by_user_id FROM users WHERE user_id = 123)
     AND u.referred_by_user_id IS NOT NULL -- 避免起始用户没有推荐人的情况
   

2. 递归终止条件：WHERE rc.depth < X是核心，它确保递归只进行到X层就停止，不会无限循环（当然，Postgres默认有递归深度限制，但手动控制更稳妥）。

3. 处理空值：如果某个用户的referred_by_user_id为空，递归JOIN时会自动跳过该层，不会继续往上追溯，完全符合你的需求。

动态深度的灵活实现：用函数封装 #

如果需要经常动态调整查询的用户ID和深度，把逻辑封装成函数会更方便：

CREATE OR REPLACE FUNCTION get_referral_chain(target_user_id INT, max_depth INT)
RETURNS TABLE(user_id INT, referred_by_user_id INT, depth INT) AS $$
BEGIN
    RETURN QUERY
    WITH RECURSIVE referral_chain AS (
        SELECT 
            user_id,
            referred_by_user_id,
            0 AS depth
        FROM users
        WHERE user_id = target_user_id
        UNION ALL
        SELECT 
            u.user_id,
            u.referred_by_user_id,
            rc.depth + 1 AS depth
        FROM users u
        JOIN referral_chain rc ON u.user_id = rc.referred_by_user_id
        WHERE rc.depth < max_depth
    )
    SELECT * FROM referral_chain;
END;
$$ LANGUAGE plpgsql;

调用函数时直接传入参数即可：

-- 查询user_id=123的最多4级推荐人
SELECT * FROM get_referral_chain(123, 4);

这种方式比用LIMIT精准得多，能保证你拿到的是完整的X层推荐链条，不会出现截断或遗漏的情况。

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