SQLの共通テーブル式（CTE）、WITHの使い方について。

CTEの使い方

下記のように使う。

WITH CTE1 AS (
    SELECT ...
    FROM ...
),
CTE2 AS (
    SELECT ...
    FROM ...
)
SELECT ...
FROM CTE2
...

サンプルSQL

WITH ManagerEmployees AS (
    SELECT
        e.EmployeeID,
        e.Name AS EmployeeName,
        m.Name AS ManagerName
    FROM
        Employees e
    JOIN
        Employees m ON e.ManagerID = m.EmployeeID
),
DepartmentCounts AS (
    SELECT
        d.DepartmentID,
        d.Name AS DepartmentName,
        COUNT(e.EmployeeID) AS EmployeeCount
    FROM
        Departments d
    LEFT JOIN
        Employees e ON d.DepartmentID = e.DepartmentID
    GROUP BY
        d.DepartmentID, d.Name
)
SELECT
    me.EmployeeName,
    me.ManagerName,
    dc.DepartmentName,
    dc.EmployeeCount
FROM
    ManagerEmployees me
JOIN
    DepartmentCounts dc ON me.EmployeeID = dc.DepartmentID
WHERE
    dc.EmployeeCount > 5;

参考