WITH問い合わせ（共通テーブル式・ＣＴＥ）基本

自己都合によりマニュアルへのリンクは9.2としています。ご了承下さい。

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ WITH問い合わせ（共通テーブル式・ＣＴＥ）基本
■■■■
■■■■
■■■■ 2013/02/03
■■■■ 使用環境：PostgreSQL9.1.7 (CentOS6.2)
                                                                   (C) 2013 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━


【マニュアル】

WITH問い合わせ（共通テーブル式）→●[マニュアル]


【参考記事】

新しい業界標準「SQL99」詳細解説 →●[記事]

Oracle11gでの対応状況→●[記事]


【用語等】

WITH問い合わせ／共通テーブル式／ＣＴＥ　ともに同じ意味




■１■ 概要
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

WITH問い合わせ（共通テーブル式またはCTEともいう)は、ひとつのＳＱＬ文専用に
その場限りの一時テーブルを作成した上で、目的のＳＱＬを実行する方法。

WITH句の中で一時テーブルを定義し、その後の主問い合わせ(SELECT文など処理本体）
でそれを使う。

なお、WITH句の中にはSELECTだけでなくINSERT、UPDATE、DELETEも可能。


【参考】一時テーブル

マニュアルではWITH問い合わせで作成されるものを「一時テーブル」と呼んでいるが、
普通、「一時テーブル」といえば以下のものなので注意。

一時テーブル→●[マニュアル]
一時テーブル→●[マニュアル]




■２■ 基本
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

以下は記述方法を説明するだけの意味のない内容になっているが、
実際には１個のSELECT文の中に同じ問い合わせを繰り返しするような用途などで用いる。


【□】 準備

DROP TABLE t1;CREATE TABLE t1 (c1 int,c2 int);INSERT INTO t1 values (1,1000),(2,2000),(3,3000);


【□】 基本（処理内容に意味はありません）

WITH tmp1 AS (
        SELECT * from t1 where c1>1
    ) 
SELECT * from tmp1;


 c1 |  c2
----+------
  2 | 2000
  3 | 3000
(2 行)


・・・この例では、SELECTした結果をtmp1という表に収め、それを主問い合わせの中でSELECTしている。
　　　上の例では意味はないが、主問い合わせの中で「tmp1」を繰り返し使うような場合などに効果的。
　　　（性能面でのメリットではなく、簡潔な記述になる。）



【□】 テーブルを２つ使う（処理内容に意味はありません）


WITH tmp1 AS (
         SELECT * from t1 where c1>1
     ), 
     tmp2 AS (
         SELECT * from tmp1 where c1>2
     )
SELECT * from tmp2;


 c1 |  c2
----+------
  3 | 3000
(1 行)


・・・この例では、SELECTした結果を「tmp1」、さらに「tmp1」をSELECTした結果を「tmp2」に収め、
      それを主問い合わせの中でSELECTしている。




■３■ 再帰(WITH RECURSIVE)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━


【□】 基本

WITH RECURSIVE tmp(c1) AS (
  SELECT 1 
  UNION ALL 
  SELECT c1+1 FROM tmp WHERE c1 < 10
)
SELECT * FROM tmp;


database1-# SELECT * FROM tmp;
 i
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 行)



・・・「tmp」はカラム１個(c1)のテーブル
      再帰的にWHERE条件に達するまで処理を行い、その結果を主問い合わせの中で利用している。






以上