下記のフォームからメッセージを送ることができます。
こんにちは、三流プログラマーのＫｅｎ３です。 今回は、 SQL文で順位を付け、サブクエリーの埋め込みを実験して(小細工して) バカみたいなSQL文を書いてみたいと思います。 いつもの三流的なアプローチなので、実際はアレンジして使ってください。 ※一部でも参考になればいいんだけど・・・ ※※何でもSQL文で実行するのは止めましょう・・・ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^/* * １．今回のキッカケ */
VBA系の掲示板に、 ------------ >アクセスあるいはエクセルで以下のように横方向へ展開したいのですが、 >WEB上を色々検索したのですが、これといった方法が見つかりませんでした。 >やはりムリなのでしょうか？ > >●元データ >顧客 購入商品 >顧客１ 商品Ａ >顧客１ 商品Ｂ >顧客２ 商品Ｂ >顧客２ 商品Ｃ >顧客３ 商品Ａ > >●展開イメージ >顧客１ 商品Ａ・商品Ｂ >顧客２ 商品Ｂ・商品Ｃ >顧客３ 商品Ａ --------------------- と、質問をもらった。/* * ２．分解して考えてみた */
なんか無いかなぁ・・・と思いつつ、分解して考えてみた。 >●元データ >顧客 購入商品 >顧客１ 商品Ａ >顧客１ 商品Ｂ >顧客２ 商品Ｂ >顧客２ 商品Ｃ >顧客３ 商品Ａ ここから、AとBの２つの集合を作ってみます。 Aの集合 顧客 (顧客でグループ化したテーブル) 顧客１ 顧客２ 顧客３ Bの集合 商品順位付き (グループ内の商品に順位を付ける) 顧客 購入商品 順位 顧客１ 商品Ａ 0 顧客１ 商品Ｂ 1 顧客２ 商品Ｂ 0 顧客２ 商品Ｃ 1 顧客３ 商品Ａ 0 AとBこれを下記のようにつなげて、 顧客１ 順位0 順位1 順位2 順位3 顧客２ 順位0 順位1 順位2 順位3 顧客３ 順位0 順位1 順位2 順位3 やれば、横の数が固定ならSQL文で書けるのかなぁ といった発想でチャレンジしてみます。 ※かなり強引ですが・・・・/* * ３．SQLでCountを使用して順位を付けてみた */
過去に、 No.088 Access クエリー DCount関数で順位付け http://www.ken3.org/vba/backno/vba088.html の ３．グループ別に順位を付ける で、 fld1が同じ、fld2で順位を０から付けてます 順位: DCount("fld2","TEST_TABLE","fld1 = '" & [fld1] & "' And fld2 <" & [fld2]) fld1 fld2 fld3 順位 あ 2 a 1 あ 1 b 0 あ 4 c 2 い 5 a 1 い 6 b 2 い 4 c 0 い 10 d 3 素直に条件に、 fld1(が)= '" & [fld1] & "'" フィールド１が同じで And fld2(が) <" & [fld2]) フィールド２が自分より小さい そんなレコードを数えて順位として演算フイールドに書いてます。 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 下記のテストデータを作成して、テストしてみます。 テーブル名:T_DATA ID 顧客 購入商品 1 顧客１ 商品Ａ 2 顧客１ 商品Ｂ 3 顧客２ 商品Ｂ 4 顧客２ 商品Ｃ 5 顧客３ 商品Ａ 6 三流君 VBA解説本 7 三流君 DVD2005年横浜優勝 8 三流君 ASP入門 9 三流君 SQLの技 今回の場合は、顧客が一緒で購入商品が<の数をカウントさせて、 DCount("購入商品","T_DATA","顧客 = '" & [顧客] & "' And 購入商品 < '" & [購入商品] & "'") AS 順位 で、顧客別に順位が振れるのかな。 AccessのDcount関数をSQLのCount関数を使う形に書き直し、 ()付きのサブクエリーとして埋め込み AS 順位と項目名を付けてみました。 strSQL = "Select ID, 顧客, 購入商品, " strSQL = strSQL & " (Select Count(*) From T_DATA AS T_TEMP " strSQL = strSQL & " Where T_TEMP.顧客 = T_DATA.顧客 " strSQL = strSQL & " And T_TEMP.購入商品 < T_DATA.購入商品) AS 順位" strSQL = strSQL & " From T_DATA" で、なんとか順位を振ることができました。 http://www.ken3.org/cgi-bin/test/test105-1.asp ↑でテストできます。 ポイントは、Select文の中に書いたSelect文、 副問い合わせでカウントして順位を出している部分です。 DCount関数様がやっていることを(返してくれる結果を) 単純に書いただけですが・・・/* * ４．サブのサブクエリー、サブクエリーのネスト */
strSQL = "Select ID, 顧客, 購入商品, " strSQL = strSQL & " (Select Count(*) From T_DATA AS T_TEMP " strSQL = strSQL & " Where T_TEMP.顧客 = T_DATA.顧客 " strSQL = strSQL & " And T_TEMP.購入商品 < T_DATA.購入商品) AS 順位" strSQL = strSQL & " From T_DATA" このSQLを実行すると、 http://www.ken3.org/cgi-bin/test/test105-1.asp ↓結果、 ID 顧客 購入商品 順位 1 顧客１ 商品Ａ 0 2 顧客１ 商品Ｂ 1 3 顧客２ 商品Ｂ 0 4 顧客２ 商品Ｃ 1 5 顧客３ 商品Ａ 0 6 三流君 VBA解説本 3 7 三流君 DVD2005年横浜優勝 1 8 三流君 ASP入門 0 9 三流君 SQLの技 2 が返ります、 さらに、これをサブクエリーとします。(サブのサブ、ネストします) Select文の埋め込み(サブクエリー)を使って、フィールドを作成してます。 かなり長いけど SELECT T_MOTO.顧客, ( SELECT T_000.購入商品 FROM T_DATA AS T_000 WHERE ( (T_MOTO.顧客=T_000.顧客) AND ( (Select Count(*) From T_DATA AS T_TEMP Where T_TEMP.顧客 = T_000.顧客 And T_TEMP.購入商品 < T_000.購入商品))=0); ) AS 商品0, ( SELECT T_000.購入商品 FROM T_DATA AS T_000 WHERE ( (T_MOTO.顧客=T_000.顧客) AND ( (Select Count(*) From T_DATA AS T_TEMP Where T_TEMP.顧客 = T_000.顧客 And T_TEMP.購入商品 < T_000.購入商品))=1); ) AS 商品1, ( SELECT T_000.購入商品 FROM T_DATA AS T_000 WHERE ( (T_MOTO.顧客=T_000.顧客) AND ( (Select Count(*) From T_DATA AS T_TEMP Where T_TEMP.顧客 = T_000.顧客 And T_TEMP.購入商品 < T_000.購入商品))=2); ) AS 商品2 FROM T_DATA AS T_MOTO Group By 顧客; ※あまりお奨めできないけど、Select文の中にさらに条件を埋め込んでます・・・ http://www.ken3.org/cgi-bin/test/test105-2.asp ↑でテストできます、見て笑ってください/* * ５．終わりの挨拶 ＜／ＨＴＭＬ＞ */
今回は、 強引なSQLでみなさん疲れたと思います。 いつもの読者の心の声(クレーム)が聞こえてきました、 >>あんな偏屈な三流的SQL文を発行するなら、自分で判断したほうがハヤイよ 確かに・・・ >>他にも書き方あるよ・・・ >>クロス集計は使えないのか？ と読者の声が聞こえてきたところで、今日も逃げるかな。 う〜ん・・今回も、中途半端ですが、 何かの参考となれば幸いです。 ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。
ここまで、読んでいただきどうもです。目的の情報が見つかったか？少々心配しつつ、、、
感想や質問・要望・苦情など 三流君へメッセージを送る。
下記のフォームからメッセージを送ることができます。
[三流君(TOP ken3.org へ戻る)]
/ [ASPで遊ぶ、失敗する]
/ [ASP記事 バックナンバー目次]
まぁ、基本はデータの受け取りかなぁ。
・[Form等を使用したデータのやり取り]・・・ASPと言っても、HTMLの入力フォームからデータを受け取ります。POSTやGETでやりとりを押さえますか。
次は、データの入出力 で ADOを使った(ADOで接続) と SQLの解説を少々
・[ADOでMdbファイルを使う]・・・MDBと接続して、簡単な追加・更新・削除を行った。
・[ADOでExcelと接続してみた]・・・.xlsと接続してSQLを使ってみた。
・[ADOでCSVと接続してみた]・・・.CSV テキストを読み出した。※更新・削除はできません
広告:
DBが使えるので、あまり使用しないけど、普通のテキストファイル処理
・[テキストファイル処理]・・・ファイルを開いて、書き込む。１行読み込みなどを軽く
VBScriptでFormat関数が無いなど、微妙にVBAと違うけど
[VBScript関数関係の説明]・・・少し、処理を書いてみた。
[その他処理サンプル]・・・あまり良いサンプル作れなかったけど。。。
何かの参考となれば幸いです。
[三流君(TOP ken3.org へ戻る)]
/ [ASPで遊ぶ、失敗する]
/ [ASP記事 バックナンバー目次]