[三流君] −−> [ASPで遊ぶ、失敗する] −−> [バックナンバー一覧]
−−> No.105 ADO SQL文で順位をつける And サブクエリーの埋め込みで遊ぶ

三流君ASP:ADO SQL文で順位をつける And サブクエリーの埋め込みで遊ぶ



本文(発行内容)


ADO SQL文で順位をつける And サブクエリーの埋め込みで遊ぶ

こんにちは、三流プログラマーのKen3です。

今回は、
SQL文で順位を付け、サブクエリーの埋め込みを実験して(小細工して)
バカみたいなSQL文を書いてみたいと思います。

いつもの三流的なアプローチなので、実際はアレンジして使ってください。
※一部でも参考になればいいんだけど・・・

※※何でもSQL文で実行するのは止めましょう・・・
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

/* * 1.今回のキッカケ */

VBA系の掲示板に、 ------------ >アクセスあるいはエクセルで以下のように横方向へ展開したいのですが、 >WEB上を色々検索したのですが、これといった方法が見つかりませんでした。 >やはりムリなのでしょうか? > >●元データ >顧客 購入商品 >顧客1 商品A >顧客1 商品B >顧客2 商品B >顧客2 商品C >顧客3 商品A > >●展開イメージ >顧客1 商品A・商品B >顧客2 商品B・商品C >顧客3 商品A --------------------- と、質問をもらった。

/* * 2.分解して考えてみた */

なんか無いかなぁ・・・と思いつつ、分解して考えてみた。 >●元データ >顧客 購入商品 >顧客1 商品A >顧客1 商品B >顧客2 商品B >顧客2 商品C >顧客3 商品A ここから、AとBの2つの集合を作ってみます。 Aの集合 顧客 (顧客でグループ化したテーブル) 顧客1 顧客2 顧客3 Bの集合 商品順位付き (グループ内の商品に順位を付ける) 顧客 購入商品 順位 顧客1 商品A 0 顧客1 商品B 1 顧客2 商品B 0 顧客2 商品C 1 顧客3 商品A 0 AとBこれを下記のようにつなげて、 顧客1 順位0 順位1 順位2 順位3 顧客2 順位0 順位1 順位2 順位3 顧客3 順位0 順位1 順位2 順位3 やれば、横の数が固定ならSQL文で書けるのかなぁ といった発想でチャレンジしてみます。 ※かなり強引ですが・・・・

/* * 3.SQLでCountを使用して順位を付けてみた */

過去に、 No.088 Access クエリー DCount関数で順位付け http://www.ken3.org/vba/backno/vba088.html の 3.グループ別に順位を付ける で、 fld1が同じ、fld2で順位を0から付けてます 順位: 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] & "'" フィールド1が同じで And fld2(が) <" & [fld2]) フィールド2が自分より小さい そんなレコードを数えて順位として演算フイールドに書いてます。 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 下記のテストデータを作成して、テストしてみます。 テーブル名:T_DATA ID 顧客 購入商品 1 顧客1 商品A 2 顧客1 商品B 3 顧客2 商品B 4 顧客2 商品C 5 顧客3 商品A 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関数様がやっていることを(返してくれる結果を) 単純に書いただけですが・・・

/* * 4.サブのサブクエリー、サブクエリーのネスト */

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 顧客1 商品A 0 2 顧客1 商品B 1 3 顧客2 商品B 0 4 顧客2 商品C 1 5 顧客3 商品A 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 ↑でテストできます、見て笑ってください

/* * 5.終わりの挨拶 </HTML> */

今回は、 強引なSQLでみなさん疲れたと思います。 いつもの読者の心の声(クレーム)が聞こえてきました、 >>あんな偏屈な三流的SQL文を発行するなら、自分で判断したほうがハヤイよ 確かに・・・ >>他にも書き方あるよ・・・ >>クロス集計は使えないのか? と読者の声が聞こえてきたところで、今日も逃げるかな。 う〜ん・・今回も、中途半端ですが、 何かの参考となれば幸いです。 ASP、VBScript勉強中の三流プログラマーのKen3でした。


ページフッター

ここまで、読んでいただきどうもです。目的の情報が見つかったか?少々心配しつつ、、、

三流君へ メッセージを送る

感想や質問・要望・苦情など 三流君へメッセージを送る。
返信例 XXXXさんへ
下記のフォームからメッセージを送ることができます。


あなたのお名前(ニックネーム):さん
返信は?:

アドレス:に返事をもらいたい
感想や質問↓:


(感想や質問・要望・苦情はHPで記事に載せることがあります。)

種類別のリンク や 広告など

[三流君(TOP ken3.org へ戻る)] / [ASPで遊ぶ、失敗する] / [ASP記事 バックナンバー目次]


Blogとリンク:[三流君のMemo別館]/ [ASP 三流君のソースコード置き場]/ [Ken3Video YouTubeで動画解説]
広告:

気になった ジャンル ↓を選択してください。

まぁ、基本はデータの受け取りかなぁ。
・[Form等を使用したデータのやり取り]・・・ASPと言っても、HTMLの入力フォームからデータを受け取ります。POSTやGETでやりとりを押さえますか。

次は、データの入出力 で ADOを使った(ADOで接続) と SQLの解説を少々
・[ADOでMdbファイルを使う]・・・MDBと接続して、簡単な追加・更新・削除を行った。
・[ADOでExcelと接続してみた]・・・.xlsと接続してSQLを使ってみた。
・[ADOでCSVと接続してみた]・・・.CSV テキストを読み出した。※更新・削除はできません

広告:



DBが使えるので、あまり使用しないけど、普通のテキストファイル処理
・[テキストファイル処理]・・・ファイルを開いて、書き込む。1行読み込みなどを軽く

VBScriptでFormat関数が無いなど、微妙にVBAと違うけど
[VBScript関数関係の説明]・・・少し、処理を書いてみた。
[その他処理サンプル]・・・あまり良いサンプル作れなかったけど。。。
何かの参考となれば幸いです。



[三流君(TOP ken3.org へ戻る)] / [ASPで遊ぶ、失敗する] / [ASP記事 バックナンバー目次]


広告: