[三流君] −−＞
[ＡＳＰで遊ぶ、失敗する] −−＞
[バックナンバー一覧]
−−＞ No.105 ADO SQL文で順位をつける And サブクエリーの埋め込みで遊ぶ
三流君ASP:ADO SQL文で順位をつける And サブクエリーの埋め込みで遊ぶ
本文(発行内容)
ADO SQL文で順位をつける And サブクエリーの埋め込みで遊ぶ
こんにちは、三流プログラマーのＫｅｎ３です。
今回は、
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文を発行するなら、自分で判断したほうがハヤイよ
確かに・・・
>>他にも書き方あるよ・・・
>>クロス集計は使えないのか？
と読者の声が聞こえてきたところで、今日も逃げるかな。
う〜ん・・今回も、中途半端ですが、
何かの参考となれば幸いです。
ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。
フィードバック
ASP系の→[掲示板]←を覗く、質問を書き込む
評価・感想
三流君の主なリンク先
[アクセスランキング]
[サイトマップ]
[リンク先・相互リンク先など]
Ken3の日記(weblog) --
[プログラマー業務の愚痴]
[VBA系の話題]
[ASP系の話題]
[コンビニ系ネタ]
[その他]
その他 宣伝広告