MySQL CTE

###関連記事

- [MySQL 基礎コマンド](https://qiita.com/zgoovw_/items/2bd571fa38773de4f992)
- [MySQL 関数と応用](https://qiita.com/zgoovw_/items/9d64cc22b66696ceb1af)
- [MySQL 正規化と整合性とINDEX](https://qiita.com/zgoovw_/items/542b98ca1d2b74ff84d4)


#CTE（Common Table Expressions）とは
CTE（共通テーブル式）には`再帰的なCTE`と`再帰的ではないCTE`があります。

- `再帰的なCTE`は深い階層の処理をする場合に、クエリ内で一時的に使えるテーブルのことです。
- `再帰的ではないCTE`はサブクエリを分かりやすく書き換えるために使用します。

次のようなテーブルを例に説明します。

```sql
CREATE TABLE videos (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(100), 
  PRIMARY KEY (id)
);

CREATE TABLE comments (
  id INT NOT NULL AUTO_INCREMENT,
  video_id INT,
  comment VARCHAR(200),
  parent_id INT,
  PRIMARY KEY (id),
  FOREIGN KEY (video_id) REFERENCES videos(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

INSERT INTO videos (title) VALUES 
  ('世界最大級のグミを1人で食う！'),
  ('スライムで風呂作ってみた'),
  ('メントスで槍を作ってコーラにブッさす');

INSERT INTO comments (video_id, comment, parent_id) VALUES 
  (1, 'ついつい見に来てしまうのは何故だろうか', NULL),
  (1, '硬いグミを食うだけで１億再生行く男', NULL),
  (3, 'メントスコーラよりマヨネーズみたいなの気になった', NULL),
  (1, 'xxxxx', 2), -- '硬いグミを食うだけで１億再生行く男' へのコメント
  (1, 'しかも3分', 2),　-- '硬いグミを食うだけで１億再生行く男' へのコメント
  (1, '通報がありました。コメントを削除してください。', 4);　-- 'xxxxx'へのコメント

SELECT * FROM videos;
SELECT * FROM comments;
-- +----+--------------------------------------------------------+
-- | id | title                                                  |
-- +----+--------------------------------------------------------+
-- |  1 | 世界最大級のグミを1人で食う！                                 |
-- |  2 | スライムで風呂作ってみた                                     |
-- |  3 | メントスで槍を作ってコーラにブッさす                             |
-- +----+--------------------------------------------------------+
-- +----+----------+--------------------------------------------------+-----------+
-- | id | video_id | comment                                          | parent_id |
-- +----+----------+--------------------------------------------------+-----------+
-- |  1 |        1 | ついつい見に来てしまうのは何故だろうか           　　　　　　　　　　　　　　　　　　　|      NULL |
-- |  2 |        1 | 硬いグミを食うだけで１億再生行く男　              　　　　　　　　　　　　　　　　　　|      NULL |
-- |  3 |        3 | メントスコーラよりマヨネーズみたいなの気になった 　　　　　　　　　　　　　　　　　　　　　　　　　　|      NULL |
-- |  4 |        1 | xxxxx                                            |         2 |
-- |  5 |        1 | しかも3分                                        　　　　|         2 |
-- |  6 |        1 | 通報がありました。コメントを削除してください。  　　　　　　　　　　　　　　　　　　　　　　　　　　　　　|         4 |
-- +----+----------+--------------------------------------------------+-----------+
```

`硬いグミを食うだけで１億再生行く男　`に関するコメントだけ取得する場合、次のようになります。

```sql
SELECT * FROM comments WHERE parent_id = 2
UNION ALL
SELECT
  comments.*
FROM
  comments JOIN (
    SELECT * FROM comments WHERE parent_id = 2
  ) AS t
ON
  comments.parent_id = t.id;
  
-- +----+----------+------------------------------------+-----------+
-- | id | video_id | comment                            | parent_id |
-- +----+----------+------------------------------------+-----------+
-- |  4 |        1 | xxxxx                              |         2 |
-- |  5 |        1 | しかも3分                            |         2 |
-- |  6 |        1 | 通報がありました。コメントを削除してください。　　　　　|         4 |
-- +----+----------+------------------------------------+-----------+
```

####再帰的でないCTE
`再帰的ではないCTE`を使用して最下層の`通報がありました。コメントを削除してください。`だけ取得します。

```sql
SELECT
  comments.*
FROM
  comments JOIN (
    SELECT * FROM comments WHERE parent_id = 2
  ) AS t
ON
  comments.parent_id = t.id;

-- 上下同じ結果が取得できます。
-- 下は`再帰的ではないCTE`で、サブクエリを`WITH T`　とすることで可読性を高めます。

WITH t AS (
  SELECT * FROM comments WHERE parent_id = 2
)
SELECT
  comments.*
FROM
  comments JOIN t
ON
  comments.parent_id = t.id;
```

####再帰的なCTE
`硬いグミを食うだけで１億再生行く男　`に関するコメントの取得ですが、最初の例では`通報がありました。コメントを削除してください。　　`に対してコメントがあった場合、さらに下の階層になるため取得することができません。
そこで、どれだけコメントがネストされていったとしても`再帰的なCTE`で取得することができます。

```sql
-- WITH RECURSIVE(再帰) CTE名　とします。
WITH RECURSIVE t AS (
-- 最初に実行される処理
  SELECT * FROM comments WHERE parent_id = 2
  UNION ALL 
-- 最初の（階層の）処理をCTE（T）として、次の処理が実行されます。
-- そして次の（階層の）の処理をCTE（T）としてまた次の階層の処理というふうに、最下層まで繰り返されます。
  SELECT
    comments.*
  FROM
    comments JOIN t 
  ON
    comments.parent_id = t.id
)
SELECT * FROM t;

-- +------+----------+-----------------------------------------------+-----------+
-- | id   | video_id | comment                                       | parent_id |
-- +------+----------+-----------------------------------------------+-----------+
-- |    4 |        1 | xxxxx                                         |         2 |
-- |    5 |        1 | しかも3分                                     　　　　|         2 |
-- |    6 |        1 | 通報がありました。コメントを削除してください。　　　　　　　　　　　　　　　　　　　　　　　　　　 |         4 |
-- +------+----------+-----------------------------------------------+-----------+
```

このように`再帰的なCTE`を使用することでどれだけコメントが増えてもコードを書き換えることなく取得することができます。