如何用单条SQL递归查询员工可访问的所有嵌套子部门？

问题背景 #

你提到当前的SQL只能查询到一级子部门，无法获取更深层级的嵌套子部门，需求是用单条SQL实现递归查询员工拥有权限的所有部门（包括直接授权部门及其所有子部门，无论层级深度）。

部门表CompanyDepartment包含字段：DepartmentID（部门ID）、DepartmentDesc（部门名称）、FatherDepartmentID（父部门ID），用于构建部门层级；权限表CompanyDepartmentPermission记录员工的部门权限，关联字段PEmplID（员工ID）和DepartmentID。

原SQL的局限 #

你当前使用的SQL语句只能匹配直接授权的部门和一级子部门，无法处理多层嵌套：

SELECT B.DepartmentDesc 
FROM CompanyDepartmentPermission A 
INNER JOIN CompanyDepartment B ON A.DepartmentID=B.DepartmentID OR A.DepartmentID=B.FatherDepartmentID 
WHERE A.PEmplID = 68

解决方案：使用递归CTE（Common Table Expression） #

通过SQL Server的递归CTE可以完美解决多层嵌套部门的查询需求，它分为锚点查询和递归查询两部分：

• 锚点查询：获取员工直接拥有权限的部门（包括直接授权和作为部门负责人的情况）
• 递归查询：迭代获取每个部门的所有子部门，直到没有更深层级为止

最终可用的工作查询语句如下：

WITH DepartmentHirarchy AS (
    -- 锚点成员：获取员工直接有权限的部门
    SELECT 
        B.DepartmentID,
        DepartmentDesc,
        FatherDepartmentID,
        DepartmentResponsiblePEmplID 
    FROM CompanyDepartment AS B 
    INNER JOIN CompanyDepartmentPermission AS A ON A.DepartmentID=B.DepartmentID 
    WHERE A.PEmplID= 34 OR DepartmentResponsiblePEmplID = 34

    UNION ALL

    -- 递归成员：获取当前部门的所有子部门
    SELECT 
        nextLevel.DepartmentID,
        nextLevel.DepartmentDesc,
        nextLevel.FatherDepartmentID,
        nextLevel.DepartmentResponsiblePEmplID 
    FROM DepartmentHirarchy AS recCall 
    INNER JOIN CompanyDepartment AS nextLevel ON nextLevel.FatherDepartmentID=recCall.DepartmentID
)
SELECT * FROM DepartmentHirarchy 
ORDER BY FatherDepartmentID,DepartmentID 
GO

关键说明 #

• 锚点部分不仅包含了CompanyDepartmentPermission中直接授权的部门，还加入了DepartmentResponsiblePEmplID = 34的条件，即员工作为部门负责人也拥有该部门的权限
• 递归部分通过DepartmentHirarchy（递归CTE的别名）和CompanyDepartment关联，每次迭代获取上一层部门的子部门，直到没有子部门为止
• 最后通过ORDER BY对结果按父部门和部门ID排序，让层级结构更清晰

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