SQLの窓

2017年09月26日


MDB 接続から、MySQL と SQLServer を外部参照として結合して、Group By を使用して複雑な SQL を作成して Excel にエクスポートする SQL 文



※ SQLの窓では、Microsoft.Jet.OLEDB.4.0 使用しているので、最新の Access( .accdb ) への外部参照はできません。( C#、VB、VBScript、PHP で Microsoft.ACE.OLEDB.12.0 を使用すれば .accdb と下位の MDB 両方にアクセスできます  )

関連する記事

MDB 接続で、MySQL と SQLServer の表を結合表示する
System.Data.OleDb で select into 構文を使って簡単に Microsoft Access のデータを他の データベースにエクスポートする

SQL文全体

この SQL 文の最も大きな目的は、得意先毎の金額の集計です。その為のベースとなる取引先データは、売上伝票のデータで、伝票番号毎に売り上げた商品と金額が複数記録されています。

取引データには、得意先名や担当者名が無いので他のテーブルから参照する為に結合していますが、group by で指定した取引先コード以外は結果に表示出来ないので( MySQL は可能なようですが、通常は不可 )、max 関数を使用して得意先名と担当者の情報と住所を表示しています。

全て、取引先コードに対して必ず一つの値しか無いのでこのような記述で取り出す事ができます。
select 
	取引先コード,
	max(得意先名) as 得意先名,
	sum(金額) as 売上金額, 
	max(社員コード) as 社員コード, 
	max(氏名) as 担当者, 
	left(max(住所1) & max(住所2),30) as 住所

from 
	([ODBC;Driver={MySQL ODBC 5.3 Unicode Driver};server=localhost;database=lightbox;uid=root;pwd=password].取引データ left outer join [ODBC;Driver={MySQL ODBC 5.3 Unicode Driver};server=localhost;database=lightbox;uid=root;pwd=password].得意先マスタ on 取引データ.取引先コード = 得意先マスタ.得意先コード)
	left outer join [ODBC;Driver={SQL Server Native Client 11.0};server=localhost;database=lightbox;uid=sa;pwd=password].社員マスタ on 得意先マスタ.担当者 = 社員マスタ.社員コード

where
	Format(取引日付, 'yyyy/MM/dd') between '2005/06/01' and '2005/06/30'

group by 取引先コード

having
	max(得意先名) like '%商店%'




Microsoft Access ベースの記述

ここでは、SQL で使える関数は、Microsoft Access のルールに従う必要があるので、left 関数や、文字列の連結と日付との比較の為に Format 関数を使用しています。

このような SQL は、Microsoft の仕様に基づいて実行されて動作するので、from の 前に into [外部参照].表名 と書く事で、ある特定の RDBMS に対するエクスポートを容易に実行する事ができます。

ここでは、解りやすく Excel に出力する書式として以下のようになります
select 
	取引先コード,
	max(得意先名) as 得意先名,
	sum(金額) as 売上金額, 
	max(社員コード) as 社員コード, 
	max(氏名) as 担当者, 
	left(max(住所1) & max(住所2),30) as 住所

into [Excel 8.0;DATABASE=c:\tmp\集計関数結果出力.xls].取引先別売上

from 
	([ODBC;Driver={MySQL ODBC 5.3 Unicode Driver};server=localhost;database=lightbox;uid=root;pwd=password].取引データ left outer join [ODBC;Driver={MySQL ODBC 5.3 Unicode Driver};server=localhost;database=lightbox;uid=root;pwd=password].得意先マスタ on 取引データ.取引先コード = 得意先マスタ.得意先コード)
	left outer join [ODBC;Driver={SQL Server Native Client 11.0};server=localhost;database=lightbox;uid=sa;pwd=password].社員マスタ on 得意先マスタ.担当者 = 社員マスタ.社員コード

where
	Format(取引日付, 'yyyy/MM/dd') between '2005/06/01' and '2005/06/30'

group by 取引先コード

having
	max(得意先名) like '%商店%'




出力先のテーブルが既に存在している場合は、前もって削除しておいて下さい。Excel の場合は、テーブル名はシート名となり、ブックが無い場合は新規に作成されます。

SQL を MDB のビューとして登録

into によるエクスポートが無い SQL 文の場合は ビューとして登録が可能です。トンカチ + V のアイコンで、ビューの名前を入力するダイアログが表示されるので、その時に接続している MDB の中にビューが登録されて、後から容易に実行する事ができます。

SQL を 各 RDBMS のビューとして登録

例えば、MySQL で今回の SQL を記述すると以下のようになります。
select 
	取引先コード,
	得意先名,
	sum(金額) as 売上金額, 
	社員コード, 
	氏名 as 担当者, 
	left(concat(max(住所1),max(住所2)),30) as 住所

from 
	取引データ left outer join 得意先マスタ on 取引先コード = 得意先コード
	left outer join 社員マスタ on 担当者 = 社員コード

where
	取引日付 between '2005/06/01' and '2005/06/30'

group by 取引先コード

having
	得意先名 like '%商店%'

これを MySQL に ビューとして登録してしまえば、SQLの窓では『処理』メニューからエクスポートが各 RDBMS に対して容易に実行できるようになっています。


posted by lightbox at 2017-09-26 17:48 | Comment(0) | SQLの窓 Build C++ | このブログの読者になる | 更新情報をチェックする
バッチ処理

Microsoft Office
container 終わり

フリーフォントで簡単ロゴ作成
フリーフォントでボタン素材作成
フリーフォントで吹き出し画像作成
フリーフォントではんこ画像作成
ほぼ自由に利用できるフリーフォント
フリーフォントの書体見本とサンプル
画像を大きく見る為のウインドウを開くボタンの作成

Android SDK ポケットリファレンス
改訂版 Webデザイナーのための jQuery入門
今すぐ使えるかんたん ホームページ HTML&CSS入門
CSS ドロップシャドウの参考デモ
Google Hosted Libraries
cdnjs
BUTTONS (CSS でボタン)
イラストAC
ぱくたそ
写真素材 足成
フリーフォント一覧
utf8 文字ツール
右サイド 終わり
base 終わり