sql - 不同类型的公共(public)表表达式

Question

我正在深入学习 CTE。所以我想知道 SQL 中的 CTE 有哪些不同类型？

Answer 1

--Type 1: Using as a sub query 
    ;WITH CTE1 AS
    (
        SELECT EmployeeId, EmployeeName
        FROM Employees
    )
    SELECT * FROM CTE1

--Type 2: Using as a recursive query (ex; employee manager situation or node structure)
    ;WITH CTE3 AS
    (

        SELECT EmployeeId, ManagerId
        FROM Employees 
        WHERE EmployeeId = @EmpId
        UNION ALL
        SELECT e1.EmployeeId, e1.ManagerId
        FROM Employees e1 
                JOIN CTE3 ON e1.ManagerId = CTE3.EmployeeId
    )
    SELECT EmployeeId, ManagerId 
    FROM CTE3

--This is a slightly different syntax (not a logical difference) 
--where returning column names are specified within brackets

    ;WITH CTE2 (EmployeeId, EmployeeName) AS 
    (
        SELECT EmployeeId, EmployeeName
        FROM Employees
    )
    SELECT * FROM CTE2