500万行EMPLOYEE表：获取含活跃节点的完整层级最优查询方案

你的核心需求是捕获所有包含至少一个活跃节点（STATUS=1/2/3）的完整层级链——包括活跃节点的所有上级祖先、所有下级后代，哪怕这些节点本身状态不活跃。之前的查询只处理了直接上下级，完全覆盖不了最多5级的层级结构，必须用递归CTE（公共表表达式）来实现，同时针对500万条数据做性能优化。

核心思路 #

1. 先定位所有活跃状态的节点作为递归起点；
2. 递归向上遍历，获取这些活跃节点的所有祖先（包括根节点，比如示例中的CEO）；
3. 递归向下遍历，获取这些活跃节点的所有后代（包括深层子节点）；
4. 合并这两部分结果并去重，得到所有需要保留的节点，批量插入目标表。

具体SQL实现 #

-- 定义递归CTE，分三步：定位活跃节点、找祖先、找后代
WITH ActiveNodes AS (
    -- 第一步：筛选所有活跃状态的节点，作为递归起点
    SELECT EMPID, MANAGERID, EMPNAME, STATUS
    FROM EMPLOYEE
    WHERE STATUS IN (1, 2, 3)
),
Ancestors AS (
    -- 初始集：活跃节点自身
    SELECT EMPID, MANAGERID, EMPNAME, STATUS
    FROM ActiveNodes
    UNION ALL
    -- 递归向上找父节点，直到根节点（MANAGERID为NULL）
    SELECT e.EMPID, e.MANAGERID, e.EMPNAME, e.STATUS
    FROM EMPLOYEE e
    INNER JOIN Ancestors a ON e.EMPID = a.MANAGERID
),
Descendants AS (
    -- 初始集：活跃节点自身
    SELECT EMPID, MANAGERID, EMPNAME, STATUS
    FROM ActiveNodes
    UNION ALL
    -- 递归向下找子节点，直到最底层叶节点
    SELECT e.EMPID, e.MANAGERID, e.EMPNAME, e.STATUS
    FROM EMPLOYEE e
    INNER JOIN Descendants d ON e.MANAGERID = d.EMPID
)
-- 合并祖先和后代结果，去重后插入目标表
INSERT INTO TargetEmployeeTable (EMPNAME, EMPID, MANAGERID, STATUS)
SELECT DISTINCT EMPNAME, EMPID, MANAGERID, STATUS
FROM (
    SELECT * FROM Ancestors
    UNION ALL
    SELECT * FROM Descendants
) AS CombinedResults
ORDER BY EMPID;

针对500万条数据的性能优化 #

因为数据量极大，必须通过索引和查询调优避免全表扫描：

• 给MANAGERID建非聚集索引：递归过程中频繁通过MANAGERID关联父/子节点，索引能将递归的时间复杂度从O(n²)降到接近O(n)；
• 给STATUS建过滤索引：创建CREATE INDEX IX_Employee_ActiveStatus ON EMPLOYEE(EMPID, MANAGERID, EMPNAME, STATUS) WHERE STATUS IN (1,2,3);，让ActiveNodes的筛选直接命中索引，无需全表扫描；
• 限制递归深度：因为层级最多5级，数据库的递归默认深度（比如SQL Server默认100）完全足够，不会出现递归溢出问题；
• 批量插入：用INSERT ... SELECT的方式直接写入目标表，比逐条插入效率高几个数量级。

为什么之前的查询错误？ #

你之前的SQL只能覆盖活跃节点本身和活跃节点的直接上级，完全处理不了：

• 深层级的祖先（比如示例中Emp3.1.2的上级Mgr3）；
• 活跃节点的下属（比如示例中Mgr1的下属SubMgr1.1的下级Emp1.1.1）；
  递归CTE则能完整遍历整个层级链，确保所有关联节点都被捕获。

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