如何在SQL Server 2012中统计每个人的完整下属链人数？

原帖子仅统计每个层级的直接下属人数，但实际需要统计每个人的完整下属链（所有层级的下属总数），当前使用的查询返回结果不正确。使用环境为SQL Server 2012，真实数据集约2500行（人员），包含多分支，层级约7级。

样本数据 #

IF OBJECT_ID (N'[tmpPeople]', N'U') IS NOT NULL
DROP TABLE [tmpPeople];

CREATE TABLE [tmpPeople]
(
    [PositionID] [float] NULL,
    [PreferredName] [varchar](max) NULL,
    [ManagerPositionID] [float] NULL
) 
GO

    
INSERT [tmpPeople] ([PreferredName], [PositionID], [ManagerPositionID]) VALUES (N'Person A', 1, 0)
INSERT [tmpPeople] ([PreferredName], [PositionID], [ManagerPositionID]) VALUES (N'Person B', 2, 1)
INSERT [tmpPeople] ([PreferredName], [PositionID], [ManagerPositionID]) VALUES (N'Person C', 3, 1)
INSERT [tmpPeople] ([PreferredName], [PositionID], [ManagerPositionID]) VALUES (N'Person D', 4, 2)
INSERT [tmpPeople] ([PreferredName], [PositionID], [ManagerPositionID]) VALUES (N'Person E', 5, 3)
INSERT [tmpPeople] ([PreferredName], [PositionID], [ManagerPositionID]) VALUES (N'Person F', 6, 4)
INSERT [tmpPeople] ([PreferredName], [PositionID], [ManagerPositionID]) VALUES (N'Person G', 7, 5)
INSERT [tmpPeople] ([PreferredName], [PositionID], [ManagerPositionID]) VALUES (N'Person H', 8, 6)
INSERT [tmpPeople] ([PreferredName], [PositionID], [ManagerPositionID]) VALUES (N'Person I', 9, 7)
INSERT [tmpPeople] ([PreferredName], [PositionID], [ManagerPositionID]) VALUES (N'Person J', 10, 8)
INSERT [tmpPeople] ([PreferredName], [PositionID], [ManagerPositionID]) VALUES (N'Person K', 11, 3)
INSERT [tmpPeople] ([PreferredName], [PositionID], [ManagerPositionID]) VALUES (N'Person L', 12, 3)
INSERT [tmpPeople] ([PreferredName], [PositionID], [ManagerPositionID]) VALUES (N'Person M', 13, 11)
INSERT [tmpPeople] ([PreferredName], [PositionID], [ManagerPositionID]) VALUES (N'Person N', 14, 11)
INSERT [tmpPeople] ([PreferredName], [PositionID], [ManagerPositionID]) VALUES (N'Person O', 15, 13)
INSERT [tmpPeople] ([PreferredName], [PositionID], [ManagerPositionID]) VALUES (N'Person P', 16, 12)
INSERT [tmpPeople] ([PreferredName], [PositionID], [ManagerPositionID]) VALUES (N'Person Q', 17, 12)
INSERT [tmpPeople] ([PreferredName], [PositionID], [ManagerPositionID]) VALUES (N'Person R', 18, 14)    

执行SELECT * FROM tmpPeople返回结果：

当前使用的CTE查询语句 #

;WITH ChildrenCTE AS (
   SELECT PositionID, PreferredName, ManagerPositionID, 0 AS n
     FROM tmpPeople
    WHERE PositionID NOT IN (SELECT ManagerPositionID FROM tmpPeople
                              WHERE ManagerPositionID IS NOT NULL
                            )
    UNION ALL
   SELECT d.PositionID, d.PreferredName, d.ManagerPositionID, n+1
     FROM ChildrenCTE cte
     JOIN tmpPeople d ON d.PositionID = cte.ManagerPositionID
    WHERE n < 20
 )

SELECT cte.PositionID, cte.PreferredName
     , SUM(cte.n) AS children
  FROM ChildrenCTE AS cte
 GROUP BY cte.PositionID, cte.PreferredName
 ORDER BY  positionID 

当前查询结果 #

问题原因 #

当前查询的逻辑完全错误：

1. 遍历方向错误：从叶子节点（没有下属的员工）向上遍历经理，而非从每个员工向下遍历下属。
2. 统计逻辑错误：SUM(cte.n)是累加每个下属的层级深度，而非统计下属的人数。例如Person A的23是所有下属的层级数之和，并非实际下属人数。

正确解决方案 #

使用递归CTE从每个员工开始，向下遍历所有层级的下属，最终统计每个员工的下属总数：

;WITH EmployeeHierarchy AS (
    -- 锚点成员：获取每个员工的直接下属
    SELECT 
        e.PositionID AS ManagerID,
        e.PreferredName AS ManagerName,
        c.PositionID AS SubordinateID,
        c.PreferredName AS SubordinateName
    FROM tmpPeople e
    JOIN tmpPeople c ON e.PositionID = c.ManagerPositionID
    UNION ALL
    -- 递归成员：继续获取下属的下属，遍历完整层级链
    SELECT 
        eh.ManagerID,
        eh.ManagerName,
        c.PositionID AS SubordinateID,
        c.PreferredName AS SubordinateName
    FROM EmployeeHierarchy eh
    JOIN tmpPeople c ON eh.SubordinateID = c.ManagerPositionID
)
-- 统计每个经理的全部下属数量，同时补充无下属的员工
SELECT 
    ManagerID AS PositionID,
    ManagerName AS PreferredName,
    COUNT(DISTINCT SubordinateID) AS TotalSubordinates
FROM EmployeeHierarchy
GROUP BY ManagerID, ManagerName
UNION ALL
SELECT 
    PositionID,
    PreferredName,
    0 AS TotalSubordinates
FROM tmpPeople
WHERE PositionID NOT IN (SELECT ManagerID FROM EmployeeHierarchy)
ORDER BY PositionID;

正确结果示例 #

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