SQL 再帰CTE — 組織図・カテゴリ階層・ツリー構造の基礎

WITH RECURSIVE ancestors AS (

  -- ① アンカー部: 対象ノード（メンズスニーカー）から開始
  SELECT
    category_id,
    category_name,
    parent_id,
    0 AS level         -- 起点ノードのlevel = 0
  FROM  categories
  WHERE category_id = 6  -- メンズスニーカーを起点に指定

  UNION ALL

  -- ② 再帰部: 現ノードのparent_idをcategory_idとして持つ行（＝親）を取得
  SELECT
    c.category_id,
    c.category_name,
    c.parent_id,
    a.level + 1          -- 上に行くほどlevelが増加
  FROM  categories c
  JOIN  ancestors a ON c.category_id = a.parent_id  -- ← ボトムアップの逆JOIN

)
SELECT
  category_id,
  category_name,
  parent_id,
  level
FROM  ancestors
ORDER BY level DESC;  -- levelが大きい（上位）順にソート → ルート優先

/*
  実行順序（SQLの論理的な評価順）:
  1. アンカー部
  2. 再帰1回目
  3. 再帰2回目
  4. 再帰3回目
  5. 再帰4回目
  6. 外側クエリ
  */

WITH RECURSIVE thread AS (

  -- ① アンカー部: ルートコメント（返信でないもの）を取得
  SELECT
    comment_id,
    content,
    parent_id,
    0                AS depth,
    ''               AS indent  -- ルートのインデントは空文字
  FROM  comments
  WHERE parent_id IS NULL

  UNION ALL

  -- ② 再帰部: 子コメントを取得、ただし depth < 3 の行にのみ再帰
  SELECT
    c.comment_id,
    c.content,
    c.parent_id,
    t.depth + 1,
    REPEAT('  ', t.depth + 1)  -- depth+1 個分の2スペースを生成
  FROM  comments c
  JOIN  thread t ON c.parent_id = t.comment_id
  WHERE t.depth + 1 < 3          -- 深さ3以上への再帰を遮断（ここがガード）

)
SELECT
  comment_id,
  content,
  parent_id,
  depth,
  indent
FROM  thread
ORDER BY comment_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. アンカー部
  2. 再帰1回目
  3. 再帰2回目
  4. 再帰3回目
  5. 外側クエリ
  */

WITH RECURSIVE folder_tree AS (

  -- ① アンカー部: 各フォルダ自身を「自分の祖先」として登録
  SELECT
    folder_id AS descendant_id,  -- 子孫側のフォルダID
    folder_id AS ancestor_id     -- 自分自身も「祖先」に含める
  FROM  folders

  UNION ALL

  -- ② 再帰部: 子フォルダ → 親フォルダへの (descendant, ancestor) ペアを生成
  SELECT
    ft.descendant_id,              -- 元々の子孫フォルダIDはそのまま
    f.parent_id AS ancestor_id    -- 親フォルダが新たな「祖先」になる
  FROM  folder_tree ft
  JOIN  folders f ON f.folder_id = ft.ancestor_id
  WHERE f.parent_id IS NOT NULL   -- ルートに到達したら停止

)
SELECT
  fo.folder_id,
  fo.folder_name,
  SUM(fi.size_kb) AS total_size_kb  -- 祖先フォルダに紐づく全ファイルサイズを合算
FROM  folder_tree ft
JOIN  files fi ON fi.folder_id = ft.descendant_id  -- ファイルは descendant 側に属する
JOIN  folders fo ON fo.folder_id = ft.ancestor_id  -- フォルダ名は ancestor 側から取得
GROUP BY fo.folder_id, fo.folder_name
ORDER BY fo.folder_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. アンカー部
  2. 再帰1回目
  3. 再帰2回目
  4. 再帰3回目: 全てNULL
  5. 外側クエリ
  */

WITH RECURSIVE sub_tree AS (

  -- ① アンカー部: 全社員を「自分を根とするツリーの起点」として登録
  SELECT
    emp_id,
    manager_id,
    emp_id AS root_id,  -- この行がどの上司ツリーに属するかのラベル
    0      AS depth
  FROM  employees

  UNION ALL

  -- ② 再帰部: 直前のワーキングテーブルの emp_id を manager_id として持つ社員を追加
  SELECT
    e.emp_id,
    e.manager_id,
    s.root_id,           -- root_id は伝播させる（誰のツリーか変えない）
    s.depth + 1
  FROM  employees e
  JOIN  sub_tree s ON e.manager_id = s.emp_id

),
direct AS (                                -- ③ 直属部下数: manager_id で直接カウント
  SELECT
    manager_id,
    COUNT(*) AS direct_cnt
  FROM  employees
  WHERE manager_id IS NOT NULL
  GROUP BY manager_id
),
total AS (                                 -- ④ 全部下数: sub_tree から depth > 0（自分自身を除く）でカウント
  SELECT
    root_id,
    COUNT(*) AS total_cnt
  FROM  sub_tree
  WHERE depth > 0                          -- depth=0は自分自身なので除外
  GROUP BY root_id
)
SELECT
  e.emp_id,
  e.emp_name,
  d.direct_cnt  AS direct_reports,
  t.total_cnt   AS total_subordinates
FROM  employees e
JOIN  direct d ON d.manager_id = e.emp_id  -- 直属部下あり = 管理職のみ
JOIN  total  t ON t.root_id   = e.emp_id
ORDER BY e.emp_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. sub_tree CTE（再帰）
  2. direct CTE
  3. total CTE
  4. 外側クエリ
  */