【SQL】WITH句の使い方 もう同じSQLを書かなくてもよい!SQLを共通化する方法
WITH句を使って共通化テーブルを作る

WITH句を使用してサブクエリに名前を付けておき、共通化することによって可読性が高まり、また、パフォーマウンスの向上にもつながる。

SQLをVIEWのように定義しておき、そのテーブルを参照することができる。

例)

id科目名前点数
1数学田中さん85点
2国語鈴木さん80点
3英語山田さん88点
1英語田中さん78点
2英語鈴木さん70点
  SELECT id,科目,名前,点数
    FROM TABLEA TA
    LEFT JOIN (SELECT id,SUM(点数) AS 合計
                 FROM TABLEA
                GROUP BY id) TAG
      ON TA.id = TAG.id

結果:

id科目名前点数合計
1数学田中さん85点163点
2国語鈴木さん80点150点
3英語山田さん88点88点
1英語田中さん78点163点
2英語鈴木さん70点150点

上記クエリにWITH句を使用すると、

WITH TAG AS
 (SELECT id,SUM(点数) AS カウント
    FROM TABLEA
   GROUP BY code
 )

  SELECT id,科目,名前,点数
    FROM TABLEA TA
    LEFT JOIN TAG
      ON TA.id = TAG.id

と書ける。

複数のテーブルを指定する

複数のサブクエリに対して使う場合は、カンマで区切って指定する。

また、自身の副問合せより前に定義したWITH句のテーブルを参照できる。

  SELECT id,科目,名前,点数
    FROM TABLEA TA
    LEFT JOIN (SELECT id,SUM(点数) AS 合計
                 FROM TABLEA
                GROUP BY id) TAG
      ON TA.id = TAG.id
    LEFT JOIN (SELECT 科目,AVG(点数) AS 科目平均
                 FROM TABLEA
                GROUP BY 科目) TAA
      ON TA.科目 = TAA.科目

結果:  

id科目名前点数合計科目平均
1数学田中さん85点163点85点
2国語鈴木さん80点150点80点
3英語山田さん88点88点78.7点
1英語田中さん78点163点78.7点
2英語鈴木さん70点150点78.7点

上記クエリにWITH句を使用すると、

WITH TAG AS
 (SELECT id,SUM(点数) AS カウント
    FROM TABLEA
   GROUP BY code
 )
 ,TAA AS
 (SELECT 科目,AVG(点数) AS 科目平均
    FROM TABLEA
   GROUP BY 科目
 )

  SELECT id,科目,名前,点数
    FROM TABLEA TA
    LEFT JOIN TAG
      ON TA.id = TAG.id
    LEFT JOIN TAA
      ON TA.id = TAA.id

また、自身より前に定義したテーブルの参照

WITH TAG AS
 (SELECT id,SUM(点数) AS カウント
    FROM TABLEA
   GROUP BY code
 )
,TAA AS
 (SELECT 科目,AVG(点数) AS 科目平均
    FROM TABLEA
   GROUP BY 科目
 )
,TAB AS
 (SELECT カウント
    FROM TAG
   WHERE id = '1'
 )

SELECT *
  FROM TAB

同じテーブルを複数回指定する場合に、WITH句を使うと有効。


今日はここまで。