PostgreSQL だと、generate_series という集合生成関数があり、例えば
postgres=# select generate_series(1, 10) x;
x
----
1
2
3
4
5
6
7
8
9
10
(10 行)
こういう連番がサックリと作れる訳なのですが、SQLite3 にはこの様な関数が用意されていません。かといって、この generate_series の為に SQLite3 拡張を作って enable_load_extension する(例: Big Sky :: SQLite で twitter のタイムラインを select する。)のはちょっと辛い。しかしながらどうしても必要な場面というのは出てきます。例えば FizzBuzz とか FizzBuzz とか、あと FizzBuzz なんかでも必要ですね。
そういう場合に使えるのが with recursive です。
SQLite Query Language: WITH clause
A recursive common table expression can be used to write a query that walks a tree or graph
https://www.sqlite.org/lang_with.html#recursivecte
これを使うと、通常の SQL 文に再帰属性を付与する事が出来ます。
with recursive
generate_series(x) as (
select 1
union all
select x+1 from generate_series limit 12
) select x from generate_series;
関数的としても扱えませんし、少し面倒臭いですが無いよりはマシです。
これを使えば FizzBuzz も簡単。
with recursive
generate_series(x) as (
select 1
union all
select x+1 from generate_series limit 100
) select
case
when 0 = x % 15
then 'FizzBuzz'
when 0 = x % 5
then 'Buzz'
when 0 = x % 3
then 'Fizz'
else ''||x
end
from
generate_series
SQL Fiddle による実行結果 でもちゃんと動いています。便利ですね。
