WITH句（共通テーブル式）

公開日：2019-12-18　最終更新日：2019-12-18

WITH句（共通テーブル式）

エクセルVBAでデータベースを扱うためのSQL入門です。
前回までに、サブクエリの基本的な使い方やサブクエリをネストした場合のSQLの書き方について説明しました。

今回は、このサブクエリをより分かり易く簡潔に書くことができるWITH句について解説します。
WITH句は共通テーブル式と呼ばれ、同じサブクエリを一つにまとめることができます。

使用するテーブル定義は以下になります。

全テーブル定義とテーブル自動作成VBA

・テーブル：m_customer ・テーブル：m_item ・テーブル：t_sales ・テーブル自動作成

WITH句の構文

WITH 名前 AS (サブクエリ)

(サブクエリ)に名前を付けておくことで、その後はこの名前だけでサブクエリを使用することができます。
(サブクエリ)の結果で一時的なテーブルを作成し名前をつけてあるものとイメージして下さい。

複数のサブクエリに対して使う場合は、カンマで区切って指定します。
WITH 名前 AS (サブクエリ) [ , 名前 AS (サブクエリ) [ , 名前 AS (サブクエリ) ] ・・・]

WITH句に続けて、メインのSQLを書きます。
そこでは、WITH句で作成した名前を一つのテーブルとして扱うだけになりますので、
SQLが読みやすく簡潔になります。

WITH句の最も簡単な使用例

サブクエリ（副問合せ）

・サブクエリ（副問合せ）とは・FROM句でサブクエリを使う・WHERE句でサブクエリを使う・SELECTのカラムにサブクエリを使う・UPDATAEでサブクエリを使う・DELETEでサブクエリを使う・サブクエリ（副問合せ）の最後に

ここでのサンプルとして、以下のSQLを紹介しています。

サブクエリを使ったSQL

SELECT T.code,T.sales_date,T.item_count,TT.sum_count
  FROM t_sales T
  LEFT JOIN (SELECT code,SUM(item_count) AS sum_count
               FROM t_sales
               GROUP BY code) TT
  ON T.code = TT.code

このサブクエリをWITH句を使って書き直してみます。

WITH句を使ったSQL

WITH TT AS
  (SELECT code,SUM(item_count) AS sum_count
     FROM t_sales
     GROUP BY code) 
SELECT T.code,T.sales_date,T.item_count,TT.sum_count
  FROM t_sales T
  LEFT JOIN TT
  ON T.code = TT.code

変更点は、元のSQLのサブクエリ部分を取り出しWITH句に移動しただけになります。

(サブクエリ) TT
↓
WITH TT AS (サブクエリ)

WITH句に書き直すだけであれば、単純にこのような書き直しが可能です。

WITH句に複数のサブクエリを使用する例

縦に日付、横にコードが並ぶようなマトリックスに集計した表を作成します。

※このようにマトリックスにするには

サブクエリを使わずにもっと簡単に書くことができます。
これについては後の章で紹介しています。
あくまで、WITHの説明としてサブクエリを使ったサンプルSQLになります。

以下では、横列として'001','002','003'の3つのコードだけに絞り込んでいます。

サブクエリを使ったSQL

SELECT TDATE.sales_date
,T001.sum_count AS 'code_001'
,T002.sum_count AS 'code_002'
,T003.sum_count AS 'code_003'
  FROM
  -- 日付
  (SELECT sales_date
   FROM t_sales
   GROUP BY sales_date) TDATE
  -- code=001
  LEFT JOIN 
    (SELECT sales_date
     ,SUM(item_count) AS sum_count
     FROM t_sales
     WHERE code = '001'
     GROUP BY sales_date) T001
    ON TDATE.sales_date = T001.sales_date
 -- code=002
  LEFT JOIN 
    (SELECT sales_date
     ,SUM(item_count) AS sum_count
     FROM t_sales
     WHERE code = '002'
     GROUP BY sales_date) T002
    ON TDATE.sales_date = T002.sales_date
 -- code=003
  LEFT JOIN 
    (SELECT sales_date
     ,SUM(item_count) AS sum_count
     FROM t_sales
     WHERE code = '003'
     GROUP BY sales_date) T003
    ON TDATE.sales_date = T003.sales_date

各サブクエリごとにコメントを入れておきました。
まず、日付の一覧を取得するサブクエリを主として、
このサブクエリに、codeが001,002,003ごとのサブクエリをJOINしています。
それぞれのサブクエリをSELECTの選択カラムとすることで横展開しています。

ほぼ同様のサブクエリが3つ使われているのが分かると思います。
このSQLをWITH句を使って書き直します。

WITH句を使ったSQL

WITH  TDATE AS    (SELECT sales_date
     FROM t_sales
     GROUP BY sales_date)
 ,TSUM AS    (SELECT code,sales_date
     ,SUM(item_count) AS sum_count
     FROM t_sales
     WHERE code IN ('001','002','003')
     GROUP BY code,sales_date)
SELECT TDATE.sales_date
  ,T001.sum_count AS code_001
  ,T002.sum_count AS code_002
  ,T003.sum_count AS code_003
  FROM
  -- 日付
  TDATE
  -- code=001
  LEFT JOIN 
    TSUM T001
    ON TDATE.sales_date = T001.sales_date
    AND T001.code = '001'
  -- code=002
  LEFT JOIN 
    TSUM T002
    ON TDATE.sales_date = T002.sales_date
    AND T002.code = '002'
  -- code=003
  LEFT JOIN 
    TSUM T003
    ON TDATE.sales_date = T003.sales_date
    AND T003.code = '003'

WITH句への書き直しは、ほぼ単純な変換だけになっています。
WITH句の中の、
WHERE code IN ('001','002','003')
この条件が無いと、いったん全データを集計することになり処理時間がかかってしまいます。

WITH句を使う事で、複数のサブクエリを一つにまとめて記述することができ読みやすいSQLになります。
ただし、
サブクエリが扱いやすくなったからと言って、そもそもサブクエリを使用する必要があるかは良く考えてみましょう。
サブクエリは付け足し付け足しができるので、出来上がったSQLが継ぎはぎだらけの複雑なものになってしまう事が多々あります。
各テーブル各データの関連性を良く考えてSQLを整理するようにしましょう。

WITH句を使った再帰SQL

WITH句の中でWITH句の結果を使う事で再帰クエリを実現できます。
再帰とは、その記述の中に自信への参照を含めることでループ処理させるものです。

VBAにおける再帰処理は以下を参照してください。
再帰呼出しについて（再帰プロシージャー）

・指定数値の階乗を求める再帰VBA ・再帰プロシージャで考慮すべき事項・再帰呼び出しの実践例

練習問題24（再帰呼出し）

・マクロVBA練習問題・シンキングタイム・マクロVBA練習問題回答へ

再帰クエリは、データベースにより書き方は変わりますが、
WITH [RECURSIVE]
SQLiteも含め多くはRECURSIVE（DBにより省略可能）を指定した書き方をします。

考え方も難しくなりますので、SQL入門では今のところは扱う予定はしていませんが、
今後良いサンプルがあれば紹介することもあるかもしれません。

CASE演算子を使ったマトリックス作成のSQL

上で紹介したマトリックスを作成するSQLは、そもそももっと簡単なSQLで実現することができます。

SELECT sales_date
 ,SUM(CASE code WHEN '001' THEN item_count ELSE 0 END) AS code_001
 ,SUM(CASE code WHEN '002' THEN item_count ELSE 0 END) AS code_002
 ,SUM(CASE code WHEN '003' THEN item_count ELSE 0 END) AS code_003
  FROM t_sales
  WHERE code IN ('001','002','003')
  GROUP BY sales_date

とても簡単なSQLですし、処理時間もサブクエリのSQLより速く処理されます。
CASE演算子については以下を参照してください。

SQL関数と演算子

・集計/数値関数・文字列関数・日付時刻関数・その他の関数・演算子・CASE演算子・SQL関数と演算子の最後に

先のサブクエリのSQLはWITH句の説明用なので、これは置いとくとしても、
似たような無駄なサブクエリが使われてしまう事は多々あります。
各テーブル各データの関連性を良く考えて、適切なSQLを書くようにしましょう。

WIYH句（共通テーブル式）の最後に

VBAでデータベースを扱うことを目的として、今回まで21回に渡ってSQLを解説してきました。
・SQLite環境作成
・テーブル作成
・INSERT
・SELECT
・WHERE、GROUP、HAVING、ORDER
・JOIN
・UNION
・UPDATE
・DELETE
・インデックス作成
・トランザクション処理
・サブクエリ
・WITH句
データベースを扱う上で通常必要とされる基本的事項については一通り説明できたと思います。
今後は、さらに知っておくと便利な機能やSQLについて、参考になりそうなものがあれば随時追加していきます。

同じテーマ「SQL入門」の記事

VBAクラスの全コード：トランザクション処理
 サブクエリ（副問合せ）
サブクエリのネストとSQLコメント&整形
WITH句（共通テーブル式）
取得行数を限定するLIMIT句
 分析関数（OVER句,WINDOW句）
「ADO + VBA」でSQLを実行するときのVBAサンプル
 SQL基礎問題1：最大在庫数を持つ製品の在庫金額
 SQL基礎問題2：文字列「-nn-」のnnが偶数のみ抽出
 SQL基礎問題3：文字列の一部をキーにして集計
 SQL基礎問題4：2つのテーブルの不一致を抽出

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
その他
SQL入門
WITH句（共通テーブル式）

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

SQL入門
WITH句（共通テーブル式）