※ 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(住所１) & max(住所２),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(住所１) & max(住所２),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(住所１),max(住所２)),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 に対して容易に実行できるようになっています。
MySQL
SQL Express
SQLの窓 Build C++ のダウンロード
表示するデータはなんでもいいのですが、他の環境に依存しないように、SQLIte3 の出来上がったデータを Android Studio にインポート(Push) して使用します。MainActivity もそのまま使わずに、MainControl というクラスを作成して MainActivity を継承し、メニュー部分の記述は見なくてもいいようにしています。
※ AndroidManifest.xml の記述は、MainActivity を MainControl に変更すれば動作します
データは、以下をダウンロードして、hanbai.sqlite3 を使用して下さい。
初期データベースを作成するには、SQLite 用の ヘルパークラスを作成して以下のように onStart と onStop に処理を書いて下さい。
データの読み出しとリストビューへの表示
loadView というメソッドを作成して、onStart 内で呼び出しています。リストビューは、カスタマイズ無しの単純リストビューなので、リストの付加データーは ArrayList に保存して使用します。( Syain クラスを作成すればその必要はありません )
※ エキスパートモードで表示しています
アーカイブとカテゴリページはこのように簡単に設定できますが、タグページは HTML 設定を直接変更して、以下の『タグページでのみ表示される内容』の記述方法で設定する必要があります
