PostgreSQL递归CTE中UNION子查询统计directsCount值异常问题

• PostgreSQL的递归CTE中，UNION两侧的结果集是按列的位置顺序匹配，而非按列别名匹配。你在递归第二个分支（UNION之后的SELECT语句）中列的定义存在明显错误：
  1. 基础分支（UNION前的SELECT）共返回5列，最后一列是directsCount的统计值
  2. 递归分支你错误写了两个和levels_down相关的列：levels_down + 1和1 as levels_down，导致第五个位置的列被固定赋值为1，刚好对应最终结果里的directsCount字段，这就是所有层级>=2的记录directsCount都返回1的核心原因，也解释了没有下级的用户directsCount仍返回1的问题
• 递归分支你完全没有做directsCount的统计逻辑，也是问题原因之一

WITH RECURSIVE subordinates AS (
    SELECT u.id,
           u.company_id as "companyId",
           u.manager_id as "managerId",
           1 as levels_down,
           (select count(*) from blitz_user as ua where ua.manager_id= u.id ) as "directsCount"
    FROM blitz_user AS u
    WHERE u.manager_id = 9

    UNION ALL -- 替换UNION为UNION ALL，不需要去重时性能更高
    SELECT u.id,
           u.company_id as "companyId",
           u.manager_id as "managerId",
           s.levels_down + 1 as levels_down,
           -- 递归分支补充和基础分支一致的directsCount统计逻辑
           (select count(*) from blitz_user as ua where ua.manager_id= u.id ) as "directsCount"
    FROM blitz_user AS u
             INNER JOIN subordinates AS s ON u.manager_id = s.id
    WHERE s.levels_down < 5 -- 原条件<=5会导致多查询一层，修正为<5即可得到最大层级为5的结果
)
SELECT id, "companyId", "managerId", levels_down, "directsCount"
FROM subordinates;

如果表数据量较大，可提前聚合直接下属统计值，避免每行重复执行子查询：

WITH user_direct_count AS (
    -- 提前聚合所有用户的直接下属数
    SELECT manager_id, count(*) as "directsCount"
    FROM blitz_user
    GROUP BY manager_id
),
RECURSIVE subordinates AS (
    SELECT u.id,
           u.company_id as "companyId",
           u.manager_id as "managerId",
           1 as levels_down,
           COALESCE(udc."directsCount", 0) as "directsCount"
    FROM blitz_user AS u
    LEFT JOIN user_direct_count udc ON u.id = udc.manager_id
    WHERE u.manager_id = 9

    UNION ALL
    SELECT u.id,
           u.company_id as "companyId",
           u.manager_id as "managerId",
           s.levels_down + 1 as levels_down,
           COALESCE(udc."directsCount", 0) as "directsCount"
    FROM blitz_user AS u
    INNER JOIN subordinates AS s ON u.manager_id = s.id
    LEFT JOIN user_direct_count udc ON u.id = udc.manager_id
    WHERE s.levels_down < 5
)
SELECT id, "companyId", "managerId", levels_down, "directsCount"
FROM subordinates;

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