MySQL5环境下如何查询表中指定user_id对应的所有上级引荐记录

由于MySQL 5.x版本不支持递归CTE语法，你可以通过自定义变量的方式实现向上查询所有层级父级引荐人的需求，具体实现代码如下：

-- 替换your_table_name为实际表名，@r := 后面的数字改为你要查询的目标user_id
SELECT t.*
FROM (
    SELECT
        @r AS current_user_id,
        (SELECT @r := referral_id FROM your_table_name WHERE user_id = current_user_id) AS parent_referral_id
    FROM
        (SELECT @r := 5) init_var,
        your_table_name
    WHERE @r IS NOT NULL
) referral_chain
INNER JOIN your_table_name t ON referral_chain.current_user_id = t.user_id
-- 可根据需求调整排序规则，此处按用户ID从小到大排序
ORDER BY t.user_id ASC;

逻辑说明 #

• 初始化自定义变量@r为你要查询的目标用户ID，本例为5
• 循环遍历过程中不断将@r更新为当前用户的referral_id，直到@r为NULL（也就是到顶层没有上级的用户为止）
• 最后将生成的引荐链用户ID和原表关联，就能一次性拿到所有对应行的完整记录
• 该方案仅向上查询父级记录，不会查询下级子记录，完全符合你的需求

测试效果 #

使用你提供的样例数据执行上述语句，返回结果如下：

和你预期的结果完全一致。

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