再帰共通テーブル式で連続した値を得る

# 一人アドカレ

| 日 | ポスト |
|---:|:------|
|  1 | [一時テーブルが存在していたら削除する](https://qiita.com/sotoattanito/items/10e65399caf34fae3017) |
|  2 | [何度も使う中間テーブルに名前をつけてクエリを見やすくする](https://qiita.com/sotoattanito/items/673fb0ceb543fdd391e2) |
|  3 | [共通テーブル式と一時テーブルの参照可能な範囲の違い](https://qiita.com/sotoattanito/items/9fa81e9acc9af915600f) |
|  4 | [グループ毎に最大値や最小値を持つレコードを抽出する](https://qiita.com/sotoattanito/items/efa8296039b182ccb85b) |
|  5 |       |
|  6 | [SQL Serverのデータインポートウィザードを使って日時データをインポートする](https://qiita.com/sotoattanito/items/6dd5996499652422c32f) |
|  7 | [再帰共通テーブル式で連続した値を得る](https://qiita.com/sotoattanito/items/e881f7808b69f3e9d887) |
|  8 |       |
|  9 |       |
| 10 |       |
| 11 |       |
| 12 |       |
| 13 |       |
| 14 |       |
| 15 |       |
| 16 |       |
| 17 |       |
| 18 |       |
| 19 |       |
| 20 |       |
| 21 |       |
| 22 |       |
| 23 |       |
| 24 |       |
| 25 |       |

# 再帰共通テーブル式で連続した値を得る

## 課題
WITH句を使用した共通テーブル式を再帰的に使用し、1から100や2019/12/01から2019/12/31までなどの連続した値を出力する。

## 準備
### 実行環境
 * SQL Server 2017 Standard
 * SQL Server Management Studio 2017

## 解法
共通テーブル式については『[何度も使う中間テーブルに名前をつけてクエリを見やすくする](https://qiita.com/sotoattanito/items/673fb0ceb543fdd391e2)』で扱った。

このWITH句を使用した共通テーブル式には自己参照を含めることができ、これを再帰共通テーブル式という。自己参照を含める、というのはどういうことか説明するためにまず、仮のコードを次に示す。

再帰共通テーブル式の構造はざっくり説明すると

```sql
WITH [共通テーブルの名前] ( [共通テーブルの持つ列], ... )
  AS ( [アンカーメンバー]
       UNION
       [再帰メンバー] )
SELECT文とか...
```

となっている。

`[共通テーブルの名前]` と `[共通テーブルの持つ列]` というのは、最終的に共通テーブル式を使って出来上がったテーブルは **なんていう名前** で、 **どんな名前の列を持っている** か、を示してあげるものだ。

`[アンカーメンバー]` と　`[再帰メンバー]` はどちらもSELECT文を使う。それぞれがもつ役割は次のとおり。

```
アンカーメンバー：再帰の出発点を与える。
再帰メンバー　　：アンカーメンバーの出力を入力に受け取って、条件を満たすなら出力する。
　　　　　　　　　出力した場合、それを新しいアンカーメンバーの出力として
　　　　　　　　　再帰メンバーの処理を繰り返す。
　　　　　　　　　条件を満たさなくなったら出力をやめる。
```

つまるところ、 `アンカーメンバー` が再帰処理の開始点で、 `再帰メンバー` が再帰処理の中身、といった感じだと思っている。

そして、上記の `アンカーメンバー` の出力を `再帰メンバー` へ渡す方法として、 `[共通テーブルの名前]` を `再帰メンバー` で利用できる。これが **『自己参照を含めることができる』** ということ。

次に実際に動くクエリを示す。
このクエリは1から99までの連番を出力するクエリである。

```sql
WITH [some_tbl] ( [number] )
  AS ( SELECT 1 AS [number]   -- このSELECT文がアンカーメンバー
        UNION ALL             -- アンカーメンバーと再帰メンバーはUNION句でつなぐ
       SELECT [number] + 1    -- このSELECT文が再帰メンバー
         FROM [some_tbl]      -- ここでsome_tblというWITH句で定義したテーブルを参照している＝自己を参照している＝自己参照
        WHERE [number] < 99 ) -- 再帰の終了条件を再帰メンバーの条件に含める
SELECT [number]
  FROM [some_Tbl]
 ORDER BY [number];
```

共通テーブル式を構成するどれがどれか、というのはコメントを参照するとして、再帰メンバーのFROM句で共通テーブルの名前である[some_tbl]を参照している。これが自己参照になる。
処理の流れとしては…

 1. アンカーメンバーの `SELECT 1` が実行されて、出力の `1` が再帰メンバーに渡される
 2. 再帰メンバーがアンカーメンバーの出力の `1` を（[some_tbl]を介して）受け取り、クエリを実行する
 3. 再帰メンバーのクエリ実行結果として `2` が出力される
 4. 出力された `2` を再び再帰メンバーへ渡す
 5. 再帰メンバーが出力の `2` を（[some_tbl]を介して）受け取り、クエリを実行する
 6. ...ということを、再帰メンバーが出力しなくなるまで繰り返す
 7. 最後に、すべての出力をまとめて[some_tbl]として最終結果とする。

出力としては次のようになる。

| number |
|-------:|
|      1 |
|      2 |
|      3 |
|    ... |
|     97 |
|     98 |
|     99 |

なお、再帰メンバーの再帰回数が一定値を超えるとエラーとなってしまう。
その場合はクエリヒントの `MAXRECURSION` を使用することで制御が可能である。

## 感想
一時的に連番テーブルが使いたいときにDBを汚さずに使えるので良いな、と思っています。

## 参考

 * [WITH common_table_expression (Transact-SQL)](https://docs.microsoft.com/ja-jp/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15)
 * [クエリヒント (Transact-SQL)](https://docs.microsoft.com/ja-jp/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver15)