[SQL] SQLの共通表式

SQLの共通表式(CTE: common table expressions)が結構便利だけど、構文を忘れがちなのでメモ。

以下のようにWITH句の中にSELECT文を書くと、その直後のSELECTでビューのように参照できる。
名前付きの副問い合わせだと思うとわかりやすいかも。
ただし、WITHからカッコの後のSELCTまでで１つの文だから、当然見える範囲はこのSQL文の中のみになる。

with.sql

with work(v1, v2) as (
  select 1, 'foo' union all
  select 2, 'bar' union all
  select 3, 'bazz'
)
select v1
     , v2
  from work;

$ sqlite3 <with.sql
v1          v2        
----------  ----------
1           foo       
2           bar       
3           bazz      

WITH句を再帰的に使うと、連番を生成したりできる。
例えば、1から10000までを生成するのはこんな。

with_rec.sql

with rec(val) as (
  select 1
  union all
  select val + 1
    from rec
   where val < 10000
)
select val
  from rec
 order by val;

フィボナッチ数列もSQLで生成してみよう。

fibonacci.sql

with
  fib(n, n0) as (select 1, 0 union all select n + n0, n from fib limit 10)
select n from fib order by n;

$ sqlite3 <fibonacci.sql
n         
----------
1         
1         
2         
3         
5         
8         
13        
21        
34        
55        

※sqliteでwith句を使うには3.8.3以上が必要。
SQLって、事前にテーブルを作って、テストデータを用意して。。。てイメージがあるけど、これなら手軽にいろいろできて面白い。