WITH (CTE・再帰)

一時テーブル

構文

WITH 一時テーブル名A AS
  (
    SELECT
      ...
    FROM
      ...
      INNER_JOIN ...
  ),
  一時テーブル名B AS
  (
    SELECT
      ...
    FROM
      一時テーブルA
      INNER_JOIN ...
  ),
  一時テーブル名C AS
  (
    SELECT
      ...
    FROM
      一時テーブルB
      INNER_JOIN ...
  ),
SELECT
  ...
FROM
  一時テーブル名C c
GROUP BY
  ...
ORDER BY
  ...;

用途

複雑なSQLの一時テーブル部分をWITH 一時テーブル名 AS (SELECT ...)で分かりやすくする
事前に一時テーブルを定義して、その一時テーブルを元に新しい一時テーブルを作成したい場合

基本的にWITHが必要なくらい複雑な場合はAPI側で処理や分類を行う

再帰

階層構造(親子)

# 再帰はせずに自己結合
SELECT
  a.ename,
  b.ename manager
FROM
  EMP a
  LEFT JOIN emp b ON a.manager_id = b.employment_id;
※最上位の親を考慮する

階層構造(親子より多い)

# 親子関係をまとめる
WITH RECURSIVE x (ename, empno) AS (
  SELECT
    CAST(ename as char(100)),
    empno
  FROM
    emp
  WHERE
    mgr is null
  UNION ALL
  SELECT
    CAST(CONCAT(x.ename, ' - ', e.ename) as char(100)),
    e.empno
  FROM
    emp e, x
  WHERE
    e.mgr = x.empno
) SELECT ename as emp_tree from x ORDER BY ename;
※UNION ALLの前はルートレコードを決める
※ルートレコードのカラムの長さで切り取られないようにCAST(カラム名 as char(100))
※UNION ALLの後は再帰的なビューxとempテーブルを結合する

# 特定の親子関係をまとめる
WITH RECURSIVE x (ename, empno) AS (
  SELECT
    ename,
    empno
  FROM
    emp
  WHERE
    ename = 'JONES'
  UNION ALL
  SELECT
    e.ename,
    e.empno
  FROM
    emp e, x
  WHERE
    e.mgr = x.empno
) SELECT ename from x;

階層構造(親や子がいるかどうか)

SELECT
  e.ename,
  (
    SELECT
      SIGN(COUNT(*))
    FROM
      emp d
    WHERE
      (
        SELECT
          COUNT(*)
        FROM
          emp f
        WHERE
          f.mgr = e.empno
      ) = 0
  ) AS IS_LEAF,
  (
    SELECT
      SIGN(COUNT(*))
    FROM
      emp d
    WHERE
      d.mgr = e.empno
      AND
      e.mgr IS NOT NULL
  ) AS IS_BRANCH,
  (
    SELECT
      SIGN(COUNT(*))
    FROM
      emp d
    WHERE
      d.empno = e.empno
      AND
      e.mgr IS NULL
  ) AS IS_ROOT
FROM
  emp e
ORDER BY
  IS_ROOT DESC, IS_BRANCH DESC;
※SIGNは1(正の場合)か0(0の場合)か-1(負の場合)を返す
※IS_LEAFはf.mgr = e.empno(誰かのマネージャーが自分)の数が0かどうか
※IS_BRANCHはd.mgr = e.empno(誰かのマネージャーが自分)でe.mgr IS NOT NULL(自分のマネージャーがいる)の数が0かどうか
※IS_ROOTはmgr IS NULL(マネージャーがいない)がいない
※IS_ROOTが1のレコードが複数あるケースも想定する