SQL WITH句のサンプル

SQLのWITH句のサンプルです。

説明	WITH句とは
サンプル	WITH句を使用するSQL
	WITH句を使用しない場合

WITH句とは

上記は、WITH句を使用した図です。

withの後の名称１は、プログラムの変数のイメージです。
その後のかっこ内のSQLが入ります。

2つめのSQLのfromの箇所に、withの後にある名称１を指定しています。
WITH句にあるSQLをテーブル(ビュー)のように使用できます。
fromの場所にある副問合せのSQLと同じ意味になります。

WITH句にあるSQL文は、その後のselect文より先に実行されます。

メリットとしては、fromの場所にあるSQLが外に出るので見やすくなります。

WITH句は、共通テーブル式またはCTE(Common Table Expression)とも呼ばれます。
※古いバージョンのMySQLでは対応していません。

https://www.postgresql.jp/document/9.0/html/queries-with.html

WITH句を使用するSQL

WITH句を使用するSQLのサンプルです。

サンプル

以下のテーブルがあるとします。

tokuisakiテーブル
tokuisaki	YYYYMM	uriage
A社	202002	200
A社	202003	150
B社	202003	100

SQL文

WITH句を使用してデータを抽出します。

with test1 as(
select AVG(uriage) as heikin from tokuisaki
)
select b.tokuisaki,b.uriage,b.YYYYMM
from test1 a,tokuisaki b
where a.heikin < b.uriage;

1行目は、withがあります。その後のtest1はasの後にあるSQLの名前です。
任意の名前を付けれます。
5行目は、1行目でつけた名前のtest1があります。テーブル(ビュー)のように使用しています。

結果は、以下のとおりです。

tokuisaki	YYYYMM	uriage
A社	202002	200

WITH句の値が複数でも可能

WITH句の値が複数でも可能です。

with test1 as(
select uriage from tokuisaki
where uriage > 100
)
select b.tokuisaki,b.uriage,b.YYYYMM
from test1 a,tokuisaki b
where b.uriage in (a.uriage);

上記のSQLでは、WITH句で複数の値を取得し、
7行目のinの条件にしています。

WITH句を使用しない場合

WITH句を使用しない場合のサンプルです。

サンプル

以下のテーブルがあるとします。

tokuisakiテーブル
tokuisaki	YYYYMM	uriage
A社	202002	200
A社	202003	150
B社	202003	100

SQL文

from句にある副問合せを使用してデータを抽出します。

select b.tokuisaki,b.uriage,b.YYYYMM
from (select AVG(uriage) as heikin from tokuisaki) a,tokuisaki b
where a.heikin < b.uriage;

2行目は、from句に売上の平均を求める副問合せがあります。
副問合せの結果をテーブルのように使用しています。

from句の中に、SQLがあるのでWITH句よりみやすさは落ちています。

結果は、以下のとおりです。

tokuisaki	YYYYMM	uriage
A社	202002	200