WITH（CTE）

-- CTEを定義して使用します。
WITH CTE名 AS (
    SELECT ...
)
SELECT * FROM CTE名;

-- 複数のCTEを定義します。
WITH
    CTE名1 AS (SELECT ...),
    CTE名2 AS (SELECT ... FROM CTE名1)
SELECT * FROM CTE名2;

-- 再帰CTEを定義します（MySQL 8.0以降・PostgreSQL）。
WITH RECURSIVE CTE名 AS (
    -- 初期クエリ（アンカー）
    SELECT ...
    UNION ALL
    -- 再帰クエリ
    SELECT ... FROM CTE名 WHERE 終了条件
)
SELECT * FROM CTE名;

-- 月別売上と全体平均を比較します。
WITH monthly_sales AS (
    SELECT
        EXTRACT(MONTH FROM ordered_at) AS month,
        SUM(total) AS sales
    FROM orders
    WHERE EXTRACT(YEAR FROM ordered_at) = 2025
    GROUP BY month
),
avg_sales AS (
    SELECT AVG(sales) AS average FROM monthly_sales
)
SELECT
    ms.month,
    ms.sales,
    av.average,
    ms.sales - av.average AS diff
FROM monthly_sales ms, avg_sales av
ORDER BY ms.month;

-- 再帰CTEで1〜10の連番を生成します（MySQL 8.0以降）。
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 10
)
SELECT n FROM seq;

-- 再帰CTEで社員の上司→部下の階層を展開します。
WITH RECURSIVE org AS (
    SELECT employee_id, name, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, org.depth + 1
    FROM employees e
    JOIN org ON e.manager_id = org.employee_id
)
SELECT depth, name FROM org ORDER BY depth, name;

-- WITH RECURSIVE seq AS (...) SELECT n FROM seq; の結果例
-- +----+
-- | n  |
-- +----+
-- |  1 |
-- |  2 |
-- |  3 |
-- | .. |
-- | 10 |
-- +----+

-- 階層展開クエリの結果例
-- +-------+----------+
-- | depth | name     |
-- +-------+----------+
-- |     0 | 山田社長 |
-- |     1 | 田中部長 |
-- |     1 | 鈴木部長 |
-- |     2 | 佐藤課長 |
-- +-------+----------+

非再帰CTEの『WITH ... AS』構文は MySQL（8.0以降）・PostgreSQL・Oracle・SQL Server・SQLite（3.8.3以降）で共通して使用できます。

-- 非再帰CTE（主要データベース共通）。
WITH monthly_sales AS (
    SELECT
        EXTRACT(MONTH FROM ordered_at) AS month,
        SUM(total) AS sales
    FROM orders
    WHERE EXTRACT(YEAR FROM ordered_at) = 2025
    GROUP BY month
)
SELECT * FROM monthly_sales;

再帰CTEでは MySQL・PostgreSQL・SQLite が『WITH RECURSIVE』キーワードを必要とします。Oracle・SQL Server は『RECURSIVE』キーワードなしで再帰CTEを記述できます。

-- 再帰CTE（MySQL・PostgreSQL・SQLite）。
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 10
)
SELECT n FROM seq;

-- 再帰CTE（Oracle・SQL Server）— RECURSIVE キーワード不要。
WITH seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 10
)
SELECT n FROM seq;

CTEはサブクエリをFROM句に書き続けると読みにくくなる複雑なクエリを整理するために有効です。一度定義したCTEは同一クエリ内で複数回参照でき、コードの重複を避けられます。

再帰CTEはツリー構造（組織図・カテゴリ階層・パーツ展開など）の走査に特に便利です。無限ループを避けるため終了条件（WHERE句）を必ず記述してください。MySQLでは再帰の最大深度を『max_sp_recursion_depth』システム変数で制御できます。

一時的なクエリ結果をビューとして保存したい場合は『VIEW』を参照してください。集合の結合には『UNION / INTERSECT / EXCEPT』も参照してください。