makoto_blog

パパさん_blog

プログラミング勉強メモや雑記など。

SQL 再帰with句

やってみた系である。

参考サイト

図でイメージするOracle DatabaseのSQL全集 第7回 再帰with句

再帰

再帰とは、鏡に鏡をうつすと無限に、というようなやつと思います。
有名なフィボナッチ関数も、自分自身を呼んでますね。

def f(x)
  p x
  if x <= 1
      x
  else
      f(x - 1) + f(x - 2)
  end
end

p("result:" + f(5).to_s)

実行結果

$ ruby fib.rb 
5
4
3
2
1
0
1
2
1
0
3
2
1
0
1
"result:5"

こういった再帰処理を、with句を使ったSQLで出来るみたいです。

再帰with句

参考サイトはoracleですが、手元のsqliteでやってみます。
ちなみにsqliteoracleのようにdual表がないようなのでfrom dual というのは省略できます。

これを実行してみます。

with rec(Val) as(
-- 初期クエリ
select 1
union all
-- 再帰クエリ
select Val+1
  from rec
 where Val+1 <= 5)
-- メインクエリ
select Val from rec;

実行結果

sqlite> with rec(Val) as(
   ...> -- 初期クエリ
   ...> select 1
   ...> union all
   ...> -- 再帰クエリ
   ...> select Val+1
   ...>   from rec
   ...>  where Val+1 <= 5)
   ...> -- メインクエリ
   ...> select Val from rec;
1
2
3
4
5

union all の上を非再帰項、下が再帰項と呼ぶようです。

最初に再帰項(初期クエリ)が実行され、その結果を使って再帰クエリを実行し、その結果を使って再帰クエリを、、と、
再帰クエリの結果が0件になるまで行います。

これを使えば、たとえば組織同士の親子関係を持つ表から、ある組織の子孫まで取得できそうですね。