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 Access SQL では、DISTINCT 集計関数の参照がサポートされていません

Microsoft ドキュメント

Microsoft Access SQL と ANSI SQL の比較

代替として副問合せである、いわゆる『インラインビュー』を使用 2つ目と3つ目が『インラインビュー』ですが、これは Oracle では正式に使用される呼び名です。
select count(distinct 商品分類) from 商品マスタ
;
select count(商品分類) from (select distinct 商品分類 from 商品マスタ) A
;
select count(*) from (select distinct 商品分類 from 商品マスタ) A
;
select '----------','----------','----------','----------'
;
select * from コード名称マスタ

一つ目の実行が、Microsoft Access SQL で失敗します( MySQL や SQLServer等 では成功します )
Syntax error (missing operator) in query expression 'count(distinct 商品分類)'
ですから、distinct を使ったSQL 全体を括弧で囲んで、from の後ろに置いて別名を付けて実行します。 NULL を含むデータ ここでは、商品分類に NULL を含む場合があるので、集計関数を商品分類に使用した場合はカウントから除外されます。但し、行全体の数を取る count(*) では含まれます。 SQLの窓のコマンドエリアでのセミコロン act ボタンで実行した場合、セミコロンは SQL の実行単位の区切りとみなされ、全ての実行を行って下段に全て表示します MySQL SQL Express SQLの窓 Build C++ のダウンロード
タグ:MDB access DISTINCT
posted by lightbox at 2017-09-26 13:38 | Comment(0) | SQLの窓 Build C++ | このブログの読者になる | 更新情報をチェックする

MDB 接続で、MySQL と SQLServer の表を結合表示する



select

	*

from

	[ODBC;DSN=SQS32;Database=lightbox;UID=sa;PWD=password].得意先マスタ

left outer join 

	[ODBC;DSN=MYSQL32;Database=lightbox;UID=root;PWD=password].社員マスタ

on 

	得意先マスタ.担当者 = 社員マスタ.社員コード


where Format(生年月日, 'yyyy/MM/dd') = '2000/01/01'



ADO や OleDb 接続では、外部のデーターベースを容易に参照する事が可能です
関連する記事

System.Data.OleDb で select into 構文を使って簡単に Microsoft Access のデータを他の データベースにエクスポートする
SQLの窓では、同梱の販売管理C.MDB をテーブル一覧部分にドラッグ&ドロップすると容易に接続可能です。
関連する記事

SQLの窓 Build C++ : 準備不要の 2000 〜 2003 の .MDB へのアクセス
MDB への接続状態で、サンプルのような SQL をコマンドエリアに入力して、『コマンドエリアの左下の実行ボタン』をクリックして下さい。そうすると、下のエリアに結果が表示されます。 (actボタンは、セミコロンを複数のSQLの分割に使用するので、実行ボタンを使用します) 但し、この SQL は、Microsoft Access の SQL の構文で書く必要があるので、サンプルとして付加した条件部分は、Format 関数が使用されています。 また、さらに、into を付加して、他のデータベースにエクスポートする事ができます(キーはエクスポートされません) この SQL の処理は、C# ゃ VB、VBScript(ADO)、PHP(ADO) でアプリケーションとして実行可能です(SQLの窓内では ADO を使用しています) Microsoft Access の外部データ Microsoft Access では、外部データとして ODBC 経由である程度のデータベースは直接リンクする事ができます。書き方のドキュメントはありませんが、mdb 時代のシステムテーブルには、接続情報としてこのような記述が使われており、現在でもある程度ものは取得できます。(MSysObjects より) ※ SQLの窓は古いアプリケーションなので、MDB 接続ですが、.accdb 接続でも同じです SQLの窓 Build C++ のダウンロード
posted by lightbox at 2017-09-26 01:55 | Comment(0) | SQLの窓 Build C++ | このブログの読者になる | 更新情報をチェックする

2013年06月14日


SQLの窓 Build C++ : 準備不要の 2000 〜 2003 の .MDB へのアクセス



インストールフォルダ内の Tool\parts に、4つの 販売管理データベースがあり、そのうちの 販売管理C.mdb は、A ボタンで開いたダイアログからすぐ選択できるようになっています。



(1) 販売管理.mdb 
社員マスタが単純です
郵便番号マスタがありません。

(2) 販売管理B.mdb
社員マスタに自己結合用の『管理者』が追加されています
郵便番号マスタが追加されています。

(3) 販売管理C.mdb
社員マスタに生年月日が追加されています
テーブルに、入金予定データと商品集計、得意先集計、社員変更履歴が追加されています

(4) db2002_2003.mdb
Office2013 で作った空のDB を 2002〜2003 用で保存し、(3) からエクスポートしたものなので、主キーが設定されていません

MDB は、ドラッグ&ドロップで読み込む事ができます

※ テーブル一覧エリアにドラッグ&ドロップできます



A ボタンのダイアログから読み込むと、読み込んだ履歴が残りますが、ドラッグ&ドロップでは残りません。


 SQLの窓 Build C++ のダウンロード





posted by lightbox at 2013-06-14 18:54 | SQLの窓 Build C++ | このブログの読者になる | 更新情報をチェックする

SQLの窓 Build C++ : 列名リストの取得

(1) 対象となる表の選択



(2) 下段の表定義情報エリアで右クリック



カンマ区切り( または、タブ区切り、改行区切り )を選択すると、その形式でクリップボードに転送されます
▼ カンマ区切り
社員コード,氏名,フリガナ,所属,性別,作成日,更新日,給与,手当,管理者

▼ タブ区切り
社員コード	氏名	フリガナ	所属	性別	作成日	更新日	給与	手当	管理者

▼ 改行区切り
社員コード
氏名
フリガナ
所属
性別
作成日
更新日
給与
手当
管理者



 SQLの窓 Build C++ のダウンロード





posted by lightbox at 2013-06-14 15:54 | SQLの窓 Build C++ | このブログの読者になる | 更新情報をチェックする

2010年01月21日


Microsoft Jet SQL に関するMicrosoftの重要なリンク

SQL データ型( 2007 )

Microsoft Jet データベース エンジン SQL と ANSI SQL のデータ型( 2007 )

Microsoft Jet SQL と ANSI SQL との比較( Access 2007 )

 SQLの窓 Build C++ のダウンロード





タグ:MDB Jet SQL
posted by lightbox at 2010-01-21 02:45 | SQLの窓 Build C++ | このブログの読者になる | 更新情報をチェックする
Seesaa の各ページの表示について
Seesaa の 記事がたまに全く表示されない場合があります。その場合は、設定> 詳細設定> ブログ設定 で 最新の情報に更新の『実行ボタン』で記事やアーカイブが最新にビルドされます。

Seesaa のページで、アーカイブとタグページは要注意です。タグページはコンテンツが全く無い状態になりますし、アーカイブページも歯抜けページはコンテンツが存在しないのにページが表示されてしまいます。

また、カテゴリページもそういう意味では完全ではありません。『カテゴリID-番号』というフォーマットで表示されるページですが、実際存在するより大きな番号でも表示されてしまいます。

※ インデックスページのみ、実際の記事数を超えたページを指定しても最後のページが表示されるようです

対処としては、このようなヘルプ的な情報を固定でページの最後に表示するようにするといいでしょう。具体的には、メインの記事コンテンツの下に『自由形式』を追加し、アーカイブとカテゴリページでのみ表示するように設定し、コンテンツを用意するといいと思います。


※ エキスパートモードで表示しています

アーカイブとカテゴリページはこのように簡単に設定できますが、タグページは HTML 設定を直接変更して、以下の『タグページでのみ表示される内容』の記述方法で設定する必要があります

<% if:page_name eq 'archive' -%>
アーカイブページでのみ表示される内容
<% /if %>

<% if:page_name eq 'category' -%>
カテゴリページでのみ表示される内容
<% /if %>

<% if:page_name eq 'tag' -%>
タグページでのみ表示される内容
<% /if %>
この記述は、以下の場所で使用します


Windows
container 終わり

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

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