SQL (再帰 CTE) 基礎文法 (?) 最速マスター

SQL

なんか流行ってるらしいので。
他の言語をある程度知っている人はこれを読めば SQL (再帰 CTE) の基礎をマスターして SQL (再帰 CTE) を書くことができるようになります。
・・・嘘ですごめんなさい。

ぜんてい

いつもの通り、SQL Server 2005/2008 でしか試してないよ！

基礎

コメント

コメントは 2 とおり

-- 一行コメント
/*
複数行
コメント
 */
基本形
-- 1から10までの数字を表示するSQL
WITH
  -- 入力は1から10まで
  Input(f, t) AS (
    SELECT 1, 10
  )
, Seq(n) AS (
    -- 最初はInputのf
    SELECT f FROM Input
    UNION ALL
    -- それ以降は1ずつ数値をインクリメント
    SELECT
        n + 1
    FROM
        Seq
    WHERE
        -- n + 1がInputのtより小さい間出力
        n + 1 <= (SELECT t FROM Input)
  )
-- 出力
SELECT
    n
FROM
    Seq
ORDER BY
    n
;

このように、WITH Input・・・処理 (UNION ALL を使用した共通表式)・・・出力 (最終的な SELECT) の形をとります。
また、いろいろと変形が考えられ、たとえば

WITH
  Input(f, t) AS (
    SELECT 1, 10
  )
, Seq(n) AS (
    SELECT f FROM Input
    UNION ALL
    SELECT
        n + 1
    FROM
        Seq
          INNER JOIN Input ON Seq.n + 1 <= Input.t
  )
SELECT
    n
FROM
    Seq
ORDER BY
    n
;

とも記述できます。

変数の定義

変数は以下のように定義します。

変数のグループ名(変数名1, 変数名2, ...) AS (
  SELECT 変数名1の値, 変数名2の値 ...
)

Input も変数ということになります。
また、変数は定義した場所以降でどこでも使えますが、値を変更することはできません。

数値

整数も実数も使えます。

SELECT 10, 0.5
四則演算
SELECT 1 + 1
SELECT 1 - 1
SELECT 1 * 2
SELECT 5 / 2
SELECT 5.0 / 2
SELECT 5 % 2
インクリメント・デクリメント

ありません。

文字列

文字列はシングルクォートで囲みます。
プレフィックスとして N を付けると Unicode 定数になります。

SELECT 'abc'
SELECT N'abc'
文字列操作
-- 結合
SELECT 'aaa' + 'bbb' -- aaabbb
-- 分割・・・後で
-- 長さ
SELECT LEN('abcdef') -- 6
-- 切り出し
SELECT SUBSTRING('abcd', 1, 3) -- abc
-- 検索
SELECT CHARINDEX('bc', 'abcd') -- 2

分割を行う関数は無いし何返せばいいのか分からないので CTE で代用します。

WITH
  Input(s, delim) AS (
    SELECT 'aaa,bbb,ccc', ','
  )
, SplitCore(i, res, delim, s) AS (
    SELECT 0, CAST(NULL AS varchar(max)), delim, s FROM Input
    UNION ALL
    SELECT
        i + 1
      , CAST(COALESCE(LEFT(s, NULLIF(CHARINDEX(delim, s), 0) - 1), s) AS varchar(max))
      , delim
      , SUBSTRING(s, NULLIF(CHARINDEX(delim, s), 0) + 1, LEN(s))
    FROM
        SplitCore
    WHERE
        s IS NOT NULL
  )
, Split(i, res) AS (
    SELECT i, res FROM SplitCore WHERE res IS NOT NULL
  )
SELECT
    res
FROM
    Split
ORDER BY
    i

配列

配列を使いたくなったら SELECT で代用しましょう。

WITH
  Array(i, val) AS (
    SELECT 0, 100
    UNION ALL
    SELECT 1, 200
    UNION ALL
    SELECT 2, 300
  )
SELECT i, val FROM Array
UNION ALL
-- 要素の個数
SELECT COUNT(*), NULL FROM Array

-- ここらへん一体何に使うんでしょうね？わかりません
WITH
  Array(i, val) AS (
    SELECT 0, 1
    UNION ALL
    SELECT 1, 2
    UNION ALL
    SELECT 2, 3
  )
-- 先頭を取り出す
SELECT TOP 1 * FROM Array
UNION ALL
-- 末尾を取り出す
SELECT i, val FROM Array WHERE i = (SELECT MAX(i) FROM Array)
UNION ALL
-- 末尾に追加
SELECT i, val FROM Array
UNION ALL
SELECT MAX(i) + 1, 9 FROM Array

制御文

文は全体で一文なので、制御文はないです。

制御式

分岐

分岐には CASE 式を使用します。

-- 単純CASE式
CASE col
WHEN 'a' THEN 0
WHEN 'b' THEN 10
         ELSE -1
END

--検索CASE式
CASE
WHEN col = 'a'
  THEN 0
WHEN col IN('b', 'c')
  THEN 10
  ELSE -1
END
繰り返し

繰り返しには再帰 CTE を使います。
パターンとしてはいくつもありますが、文字列の分割で示した入力をループごとに減らしていく方法の他に、添え字を使用することもできます。

WITH
  -- 入力に添え字を追加する
  Input(i, s) AS (
    SELECT 0, 'aaa'
    UNION ALL
    SELECT 1, 'bbb'
    UNION ALL
    SELECT 2, 'ccc'
  )
, MaxIdx(i) AS (
    SELECT MAX(i) FROM Input
  )
, StrJoin(s, i) AS (
    SELECT
        CAST(s AS varchar(max))
      , i
    FROM
        Input
    WHERE
        i = 0
    UNION ALL
    SELECT
        CAST(S.s + I.s AS varchar(max))
        -- 添え字を増やしていく(場合によっては減らしていく)
      , S.i + 1
    FROM
        StrJoin S
          INNER JOIN Input I ON S.i + 1 = I.i
    WHERE
        -- 添え字で終了を判断
        S.i <> (SELECT i FROM MaxIdx)
  )
, Result(s) AS (
    SELECT s FROM StrJoin
    WHERE i = (SELECT i FROM MaxIdx)
  )
SELECT * FROM Result

サブルーチン・メソッド

ありません。

ファイル入出力

できません。

知っておいた方がいい○○

って何かありますかね？
え？再帰 CTE なんて使わない？ハハハ、そんなバカな。


追記：
ありました！ありましたよ知っておいた方がいい○○！

再帰の上限を設定する

再帰の上限を設定し、それ以上再帰されるとエラーになります。

WITH
  Seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT
        n + 1
    FROM
        Seq
    WHERE
        n < 150
  )
SELECT * FROM Seq
OPTION (MAXRECURSION 200)

最後の OPTION (MAXRECURSION 200) というのがそれです。
何も指定しないと 100 を指定したときと変わらないので、OPTION 部分をはずすと実行時にエラーになります。


また、0 を指定すると無制限になります。最強ですね。
再帰の上限がなくなった SQL にもはや敵など存在しないも同然です。

どれくらい最強なの？

Brainf*ck とか実装できます。
ほかにも、BrainCrash なんてものももちろん実装できます。
でも一番最強だと思ったのは、電卓ですかね。