Script事業案内エクセル以外トップSQL入門WITH句(共通テーブル式)最終更新日:2019-12-18WITH句(共通テーブル式)エクセルVBAでデータベースを扱うためのSQL入門です。前回までに、サブクエリの基本的な使い方やサブクエリをネストした場合のSQLの書き方について説明しました。今回は、このサブクエリをより分かり易く簡潔に書くことができるWITH句について解説します。WITH句は共通テーブル式と呼ばれ、同じサブクエリを一つにまとめることができます。使用するテーブル定義は以下になります。全テーブル定義とテーブル自動作成VBAデータベースの正規化とマスタの作成で作成した全テーブル定義と、テーブル名変更と列追加(ALTERTABLE)とテーブル自動作成で作成したテーブル自動作成VBAを掲載しておきます。エクセルVBAでデータベースを扱うためのSQL入門です。前回までにテーブルを作成したり、削除したりできるようになりました。(サブクエリ)(サブクエリ)に名前を付けておくことで、その後はこの名前だけでサブクエリを使用することができます。(サブクエリ)の結果で一時的なテーブルを作成し名前をつけてあるものとイメージして下さい。複数のサブクエリに対して使う場合は、カンマで区切って指定します。・・・]WITH句に続けて、メインのSQLを書きます。そこでは、WITH句で作成した名前を一つのテーブルとして扱うだけになりますので、SQLが読みやすく簡潔になります。WITH句の最も簡単な使用例サブクエリ(副問合せ)エクセルVBAでデータベースを扱うためのSQL入門です。前回までで、基本的な部分については一通り説明しました。今回は、より便利にSQLを使えるように副問合せ(サブクエリ)を解説します。サブクエリが使えるようになると、SQLのすごさがより実感として分かってくるはずです。ここでのサンプルとして、以下のSQLを紹介しています。TT.codeこのサブクエリをWITH句を使って書き直してみます。TT.code変更点は、元のSQLのサブクエリ部分を取り出しWITH句に移動しただけになります。(サブクエリ)WITH句に書き直すだけであれば、単純にこのような書き直しが可能です。縦に日付、横にコードが並ぶようなマトリックスに集計した表を作成します。※このようにマトリックスにするにはサブクエリを使わずにもっと簡単に書くことができます。これについては後の章で紹介しています。WITHの説明としてサブクエリを使ったサンプルSQLになります。以下では、横列として'001','002','003'の3つのコードだけに絞り込んでいます。T003.sales_date各サブクエリごとにコメントを入れておきました。日付の一覧を取得するサブクエリを主として、このサブクエリに、codeが001,002,003ごとのサブクエリをJOINしています。それぞれのサブクエリをSELECTの選択カラムとすることで横展開しています。同様のサブクエリが3つ使われているのが分かると思います。このSQLをWITH句を使って書き直します。'003'WITH句への書き直しは、ほぼ単純な変換だけになっています。('001','002','003')この条件が無いと、いったん全データを集計することになり処理時間がかかってしまいます。WITH句を使う事で、複数のサブクエリを一つにまとめて記述することができ読みやすいSQLになります。サブクエリが扱いやすくなったからと言って、そもそもサブクエリを使用する必要があるかは良く考えてみましょう。サブクエリは付け足し付け足しができるので、出来上がったSQLが継ぎはぎだらけの複雑なものになってしまう事が多々あります。各テーブル各データの関連性を良く考えてSQLを整理するようにしましょう。WITH句を使った再帰SQLWITH句の中でWITH句の結果を使う事で再帰クエリを実現できます。再帰とは、その記述の中に自信への参照を含めることでループ処理させるものです。VBAにおける再帰処理は以下を参照してください。再帰呼出しについて(再帰プロシージャー)再帰プロシージャとは、自分自身を呼び出すプロシージャです。プロシージャが、そのプロシージャ内で自分自身を呼び出すような処理を再帰呼び出しと呼びます。自分自身を呼び出し、終了条件を満たすまで、意図的に無限ループさせます。・以下の表の全組み合わせを作成して下さい。サンプルデータでは、組み合わせの数は、8*3*6*5=720通りです。・各項目はカンマ(,)で区切って下さい。・出力先は、新規シートを追加し、1行目に見出しとして"組み合わせ文字"と入れ、2行目より出力して下さい。[RECURSIVE]SQLiteも含め多くはRECURSIVE(DBにより省略可能)を指定した書き方をします。今後良いサンプルがあれば紹介することもあるかもしれません。CASE演算子を使ったマトリックス作成のSQL上で紹介したマトリックスを作成するSQLは、そもそももっと簡単なSQLで実現することができます。sales_dateとても簡単なSQLですし、処理時間もサブクエリのSQLより速く処理されます。CASE演算子については以下を参照してください。SQL関数と演算子エクセルVBAでデータベースを扱うためのSQL入門です。前2回でSELECTをやりましたが、その中で登場したCOUNTやSUMはSQL関数です。今回は、SQL関数と演算子について、代表的なものについて使い方を解説します。似たような無駄なサブクエリが使われてしまう事は多々あります。各テーブル各データの関連性を良く考えて、適切なSQLを書くようにしましょう。WIYH句(共通テーブル式)の最後にVBAでデータベースを扱うことを目的として、今回まで21回に渡ってSQLを解説してきました。・サブクエリ・WITH句データベースを扱う上で通常必要とされる基本的事項については一通り説明できたと思います。今後は、さらに知っておくと便利な機能やSQLについて、参考になりそうなものがあれば随時追加していきます。Next)|VBA入門7.マクロって何?VBAって何?|VBA入門8.セルに文字を入れるとは(Range,Value)|VBA入門9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説10.ひらがな⇔カタカナの変換|エクセル基本操作トップSQL入門WITH句(共通テーブル式)このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。GASスプレッドシート入門スプレッドシート応用GAS入門GAS応用トップSQL入門WITH句このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。