递归CTE外连接报错及无法查询最高管理者问题求助

Hey, let's work through why your recursive CTE isn't pulling in the top-level CEO and fix it!

The Root of the Problem #

Your original code forces a join to the boss table at every recursive step, but the CEO has a ManagerId of NULL—so there's no matching record to join to, which stops the recursion before reaching them. Plus, SQL Server explicitly blocks outer joins in the recursive part of a CTE, which is why you hit that error when trying to switch to OUTER JOIN.

Fixed Solution Code #

Here's adjusted code that will capture every level up to the CEO, without triggering the outer join error:

WITH teacherTablecte AS (
    -- Anchor member: Start with the target teacher, get their immediate manager if exists
    SELECT 
        teacher.IdTeacher, 
        teacher.FirstName, 
        teacher.LastName, 
        teacher.PhoneNumber,
        teacher.ManagerId, 
        1 AS EmpLevel,
        ISNULL(boss.FirstName + ' ' + boss.LastName, NULL) AS bossName
    FROM [dbo].[T_Teachers] teacher
    LEFT JOIN [dbo].[T_Teachers] boss ON teacher.ManagerId = boss.IdTeacher
    WHERE teacher.IdTeacher = 2011

    UNION ALL

    -- Recursive member: Traverse up to each manager, stop when there's no more ManagerId
    SELECT 
        manager.IdTeacher, 
        manager.FirstName, 
        manager.LastName, 
        manager.PhoneNumber,
        manager.ManagerId, 
        cte.EmpLevel + 1 AS EmpLevel,
        ISNULL(upperManager.FirstName + ' ' + upperManager.LastName, NULL) AS bossName
    FROM teacherTablecte cte
    -- Inner join here is safe: only continue recursion if current record has a ManagerId
    JOIN [dbo].[T_Teachers] manager ON cte.ManagerId = manager.IdTeacher
    -- Left join to get the manager's boss (no error here because this isn't the recursive join condition)
    LEFT JOIN [dbo].[T_Teachers] upperManager ON manager.ManagerId = upperManager.IdTeacher
)
SELECT * FROM teacherTablecte

Key Adjustments Explained #

1. Anchor member uses LEFT JOIN: Ensures your target teacher is returned even if they had no manager (not your case, but makes the logic more robust), while pulling their immediate boss's name if it exists.
2. Recursive join logic: The inner join to manager only runs when the current CTE record has a valid ManagerId—so recursion stops naturally when we hit the CEO (who has a NULL ManagerId). The LEFT JOIN for upperManager is allowed because it's not part of the recursive link between CTE iterations.
3. ISNULL for bossName: Sets bossName to NULL for the CEO (since they have no boss), which matches real-world data.

Run this code, and you'll get the full chain from the teacher with IdTeacher=2011 all the way up to the top-level Chief Executive.

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