本文介绍如何使用 SQL 递归公用表表达式（CTE）从 referral 表中完整检索指定用户的多级推荐关系链，解决因 CTE 与表名冲突导致的递归查询报错问题，并提供可直接在 Spring Data JPA 中使用的原生查询示例。
本文介绍如何使用 sql 递归公用表表达式（cte）从 `referral` 表中完整检索指定用户的多级推荐关系链，解决因 cte 与表名冲突导致的递归查询报错问题，并提供可直接在 spring data jpa 中使用的原生查询示例。
在用户增长系统中，常需追踪“谁邀请了谁”的多层推荐关系（如：用户1 → 用户2 → 用户6，用户1 → 用户5 → 用户7 → 用户8 → …）。这类层级结构天然适合用递归查询处理，但实践中容易因命名冲突导致数据库报错，例如 PostgreSQL 报出：
ERROR: recursive reference to query "referral" must not appear within its non-recursive term
根本原因在于：递归 CTE 的名称与基础表名完全相同（均为 referral），导致 SQL 解析器无法区分递归引用的是 CTE 自身还是原始表，从而拒绝执行。
✅ 正确做法是为递归 CTE 显式指定一个独立、无歧义的别名（如 cte），并在递归部分明确引用该别名。以下是修正后的标准 PostgreSQL 递归查询：
WITH RECURSIVE cte AS (
-- 非递归项（种子）：查找直接被 referByUserId 推荐的用户
SELECT * FROM referral WHERE refer_by = :referByUserId
UNION ALL
-- 递归项：查找由上一层推荐人（cte.refer_to）所推荐的新用户
SELECT r.*
FROM cte c
INNER JOIN referral r ON r.refer_by = c.refer_to
)
SELECT refer_by, refer_to FROM cte;
? 关键要点说明：
- cte 是递归 CTE 的唯一标识符，全程不与任何物理表重名；
- 初始查询（种子）仅匹配一级直推关系；
- UNION ALL 后的递归分支通过 cte c INNER JOIN referral r 关联，确保每次迭代都基于上一轮结果向下延伸一层；
- 查询自动终止于无新匹配记录时（即无用户被当前层级推荐人推荐）。
在 Spring Data JPA 中，可将其封装为 @Query 原生查询（注意参数占位符语法兼容性）：
@Query(value = "WITH RECURSIVE cte AS (" +
" SELECT * FROM referral WHERE refer_by = :referByUserId " +
" UNION ALL " +
" SELECT r.* FROM cte c " +
" INNER JOIN referral r ON r.refer_by = c.refer_to " +
") " +
"SELECT refer_by, refer_to FROM cte", nativeQuery = true)
List<object> findReferralChain(@Param("referByUserId") Long referByUserId);</object>
⚠️ 注意事项：
- 不同数据库语法略有差异：MySQL 8.0+、SQL Server、Oracle 均支持 WITH RECURSIVE，但旧版 MySQL（
- 深度过大的链路可能触发默认递归限制（PostgreSQL 默认 max_recursion_depth = 100），必要时可通过 SET statement_timeout TO '30s' 或调整 work_mem 优化性能；
- 若业务需返回层级深度（如第1级、第2级…），可在 CTE 中添加 level INT 列并初始化为 1，递归时 level + 1；
- 生产环境建议对 (refer_by) 和 (refer_to) 字段建立联合索引，显著提升 JOIN 效率。
掌握此模式后，你不仅能准确还原用户推荐树，还可轻松扩展至组织架构查询、物料 BOM 展开、权限继承路径分析等典型树形数据场景。
