SQL子查询返回多行值错误（Msg512）排查及层级查询解决方案

问题场景 #

用户需求：以user1@abc.com的身份，获取所有直接汇报、间接汇报的人员记录（所有层级大于等于当前用户层级的记录），目标EmployeeID包括273、16、274、285、286、275、276、23。

遇到的错误 #

最初使用的查询语句触发了Msg 512, Level 16, State 1错误：

子查询返回了多个值。当子查询跟随 =、!=、<、<=、>、>= 运算符或用作表达式时，不允许这种情况。

原始错误查询代码：

select A.ManagerID, A.ManagerEmail, A.Email, A.EmployeeID, A.Title, A.DeptID, A.Level 
from TOrganization_Hierarchy A 
where A.ManagerEmail = 'user1@abc.com' 
and A.Level >= (select B.Level from TOrganization_Hierarchy B where B.ManagerEmail = A.ManagerEmail) ;

错误原因分析 #

问题出在子查询select B.Level from TOrganization_Hierarchy B where B.ManagerEmail = A.ManagerEmail：同一个ManagerEmail对应多条员工记录时，这个子查询会返回多个Level值，而>=运算符只能接收单个值进行比较，因此触发了512错误。

解决方案：使用递归CTE处理层级结构 #

递归CTE是处理树形组织架构的标准方案，能轻松遍历所有层级的下属。以下是可正常运行的代码：

WITH CTE AS (
    SELECT 
        OH.employeeid, 
        OH.managerid, 
        OH.email AS EMPEMAIL, 
        1 AS level 
    FROM TORGANIZATION_HIERARCHY OH 
    WHERE OH.[email] = 'user1@abc.com'
    UNION ALL
    SELECT 
        CHIL.employeeid, 
        CHIL.managerid, 
        CHIL.email, 
        level + 1 
    FROM TORGANIZATION_HIERARCHY CHIL 
    JOIN CTE PARENT ON CHIL.[managerid] = PARENT.[employeeid]
), ANOTHERCTE AS (
    SELECT 
        T.[email], 
        T.[destination_account], 
        T.[customer_service_rep_code] 
    FROM [KGFGJK].[DBO].[TRANS] AS T
) 
SELECT * 
FROM ANOTHERCTE 
INNER JOIN CTE ON CTE.empemail = ANOTHERCTE.[email];

代码说明 #

1. 递归CTE（CTE）：
   • 初始查询：定位到当前用户user1@abc.com，标记层级为1。
   • 递归部分：通过managerid关联上级，逐层获取下属，层级数每次加1，直到遍历完所有下属分支。
2. ANOTHERCTE：提取交易表中的核心字段，最后通过email关联员工层级数据和交易数据，得到完整的结果集。

内容的提问来源于stack exchange，提问作者ATL-JP