SQL Server CTE

----------------嵌套获取员工级别
WITH CTE (EmployeeId,FullName, ManagerId, BossName, EmployeeLevel)
AS
(
    SELECT EmployeeId,FullName, ManagerId, e.FullName BossName, 1 EmployeeLevel
    FROM Employee e
    WHERE ManagerId = -1
    UNION ALL
    SELECT e.EmployeeId, e.FullName, e.ManagerId, em.FullName BossName, EmployeeLevel+1
    FROM 
        Employee e
        INNER JOIN CTE c ON c.EmployeeId = e.ManagerId
        INNER JOIN Employee em ON em.EmployeeId = e.ManagerId
)

SELECT * FROM CTE;
--OPTION (MAXRECURSION 12)--设置嵌套栈的大小，大于此值则报错

---------一次多表
WITH e AS
(
SELECT
    EmployeeId,
    DepartmentId,
    FullName EmployeeName
FROM
    Employee
),
et AS
(
SELECT
    *
FROM
    Department
)

SELECT 
    EmployeeId,
    EmployeeName,
    DepartmentName
FROM 
    e
    INNER JOIN et ON et.DepartmentId = e.DepartmentId