Classic ASPテストページです。
情報が古いので参考程度に読んだりテストしてみてください。
共有サーバーなので、処理に時間がかかるとタイムアウトやアクセスが集中すると落ちている時がありますがご勘弁を・・・

三流君(TOP) /ASPで遊ぶ /ASP記事一覧を見る

分類: [Form関係] /DB系ADOを使い( [Mdb接続] [CSV接続] [Excel接続]) /[ASPでTextFileを操作] /[VBScript関数ほか]

三流君:SQL サブクエリーの埋め込み(Select文のネスト)

イヤになるくらい、Select文を重ねてみます(多重・ネスト)

質問とテストデータの準備

いただいた質問は、
------
アクセスあるいはエクセルで以下のように横方向へ展開したいのですが、WEB上を色々検索したのですが、これといった方法が見つかりませんでした。やはりムリなのでしょうか?

●元データ
顧客 購入商品
顧客1 商品A
顧客1 商品B
顧客2 商品B
顧客2 商品C
顧客3 商品A

●展開イメージ
顧客1 商品A・商品B
顧客2 商品B・商品C
顧客3 商品A
------
db105.mdbにテスト用のT_DATAテーブルを作成しました。
発行するSQL文
Select * From 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の技

購入商品に順位を付けるSQLを作成

Count関数を使用して、順位を付けてみた。
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"
↑こんな感じで、Select文の中に結果を返すSelect文を書くことができます
発行するSQL文
Select ID, 顧客, 購入商品, (Select Count(*) From T_DATA AS T_TEMP Where T_TEMP.顧客 = T_DATA.顧客 And T_TEMP.購入商品 < T_DATA.購入商品) AS 順位 From T_DATA
ID顧客購入商品順位
1顧客1商品A0
2顧客1商品B1
3顧客2商品B0
4顧客2商品C1
5顧客3商品A0
6三流君VBA解説本3
7三流君DVD2005年横浜優勝1
8三流君ASP入門0
9三流君SQLの技2

Select文を重ね、強引にフィールドを作成する

顧客・商品単位の順位が付いたので、
強引に、顧客,商品0,商品1,商品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; 
※あまりお奨めできないけど、Select文の中にさらに条件を埋め込んでます・・・
発行するSQL文
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;
顧客商品0商品1商品2
顧客1商品A商品B
顧客1商品A商品B
顧客2商品B商品C
顧客2商品B商品C
顧客3商品A
三流君ASP入門DVD2005年横浜優勝SQLの技
三流君ASP入門DVD2005年横浜優勝SQLの技
三流君ASP入門DVD2005年横浜優勝SQLの技
三流君ASP入門DVD2005年横浜優勝SQLの技

さらにグループ化した

データが重複しているので、顧客でグループ化して結果を整えました。
Group By 顧客を最後に追加してやっとOKかな・・・
発行するSQL文
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 顧客;
顧客商品0商品1商品2
顧客1商品A商品B
顧客2商品B商品C
顧客3商品A
三流君ASP入門DVD2005年横浜優勝SQLの技

↑なんとか顧客別に横に商品名を出せたのかなぁ・・でもなぁ
ここまでするなら、1レコード単位で回して、自分でやったほうがいいような・・


Countを使用したサンプル(ソースの紹介)

詳細解説は[http:/asp/backno/asp105.html]を見てください
下記、/cgi-bin/test/test105-2.aspのソース(中身)です

Count を 使った ソースコードを紹介する

表示色の説明:ASP VBScriptは <% 〜 %>
キーワードはCount です。 <%@LANGUAGE=VBScript%> <html> <head><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=x-sjis"> <title>SQL サブクエリーの埋め込み(Select文のネスト)</title> </head> <body> <h1>三流君:SQL サブクエリーの埋め込み(Select文のネスト)</h1> イヤになるくらい、Select文を重ねてみます(多重・ネスト)<br> <h2>質問とテストデータの準備</h2> いただいた質問は、<br> ------<br> アクセスあるいはエクセルで以下のように横方向へ展開したいのですが、WEB上を色々検索したのですが、これといった方法が見つかりませんでした。やはりムリなのでしょうか?<br> <br> ●元データ<br> 顧客 購入商品<br> 顧客1 商品A<br> 顧客1 商品B<br> 顧客2 商品B<br> 顧客2 商品C<br> 顧客3 商品A<br> <br> ●展開イメージ<br> 顧客1 商品A・商品B<br> 顧客2 商品B・商品C<br> 顧客3 商品A<br> ------<br> db105.mdbにテスト用のT_DATAテーブルを作成しました。<br> <% strSQL = "Select * From T_DATA" Call RUN_SQL(strSQL) 'テストデータの表示 %> <h2>購入商品に順位を付けるSQLを作成</h2> Count関数を使用して、順位を付けてみた。<br> <pre>strSQL = &quot;Select ID, 顧客, 購入商品, &quot; strSQL = strSQL &amp; &quot; (Select Count(*) From T_DATA AS T_TEMP &quot; strSQL = strSQL &amp; &quot; Where T_TEMP.顧客 = T_DATA.顧客 &quot; strSQL = strSQL &amp; &quot; And T_TEMP.購入商品 &lt; T_DATA.購入商品) AS 順位&quot; strSQL = strSQL &amp; &quot; From T_DATA&quot;</pre> ↑こんな感じで、Select文の中に結果を返すSelect文を書くことができます<br> <% 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" Call RUN_SQL(strSQL) 'SQLの発行確認 %> <h2>Select文を重ね、強引にフィールドを作成する</h2> 顧客・商品単位の順位が付いたので、<br> 強引に、顧客,商品0,商品1,商品2の表を作ってみます。<br> Select文の埋め込み(サブクエリー)を使って、フィールドを作成してます。<br> かなり長いけど<br> <pre>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.購入商品 &lt; 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.購入商品 &lt; 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.購入商品 &lt; T_000.購入商品))=2); ) AS 商品2 FROM T_DATA AS T_MOTO; </pre> ※あまりお奨めできないけど、Select文の中にさらに条件を埋め込んでます・・・<br> <% ' strSQL = "Select " strSQL = strSQL & " T_MOTO.顧客, " '順位が0のデータを強引に持ってくる strSQL = strSQL & " ( SELECT T_000.購入商品 FROM T_DATA AS T_000" strSQL = strSQL & " WHERE ((T_MOTO.顧客=T_000.顧客)" strSQL = strSQL & " AND ((Select Count(*) From T_DATA AS T_TEMP" strSQL = strSQL & " Where T_TEMP.顧客 = T_000.顧客" strSQL = strSQL & " And T_TEMP.購入商品 < T_000.購入商品))=0);" strSQL = strSQL & " ) AS 商品0, " '順位が1のデータを強引に持ってくる strSQL = strSQL & " ( SELECT T_000.購入商品 FROM T_DATA AS T_000" strSQL = strSQL & " WHERE ((T_MOTO.顧客=T_000.顧客)" strSQL = strSQL & " AND ((Select Count(*) From T_DATA AS T_TEMP" strSQL = strSQL & " Where T_TEMP.顧客 = T_000.顧客" strSQL = strSQL & " And T_TEMP.購入商品 < T_000.購入商品))=1);" strSQL = strSQL & " ) AS 商品1, " '順位が2のデータを強引に持ってくる strSQL = strSQL & " ( SELECT T_000.購入商品 FROM T_DATA AS T_000" strSQL = strSQL & " WHERE ((T_MOTO.顧客=T_000.顧客)" strSQL = strSQL & " AND ((Select Count(*) From T_DATA AS T_TEMP" strSQL = strSQL & " Where T_TEMP.顧客 = T_000.顧客" strSQL = strSQL & " And T_TEMP.購入商品 < T_000.購入商品))=2);" strSQL = strSQL & " ) AS 商品2 " strSQL = strSQL & " FROM T_DATA AS T_MOTO;" Call RUN_SQL(strSQL) ' %> <h2>さらにグループ化した</h2> データが重複しているので、顧客でグループ化して結果を整えました。<br> Group By 顧客を最後に追加してやっとOKかな・・・<br> <% strSQL = "Select " strSQL = strSQL & " T_MOTO.顧客, " '順位が0のデータを強引に持ってくる strSQL = strSQL & " ( SELECT T_000.購入商品 FROM T_DATA AS T_000" strSQL = strSQL & " WHERE ((T_MOTO.顧客=T_000.顧客)" strSQL = strSQL & " AND ((Select Count(*) From T_DATA AS T_TEMP" strSQL = strSQL & " Where T_TEMP.顧客 = T_000.顧客" strSQL = strSQL & " And T_TEMP.購入商品 < T_000.購入商品))=0);" strSQL = strSQL & " ) AS 商品0, " '順位が1のデータを強引に持ってくる strSQL = strSQL & " ( SELECT T_000.購入商品 FROM T_DATA AS T_000" strSQL = strSQL & " WHERE ((T_MOTO.顧客=T_000.顧客)" strSQL = strSQL & " AND ((Select Count(*) From T_DATA AS T_TEMP" strSQL = strSQL & " Where T_TEMP.顧客 = T_000.顧客" strSQL = strSQL & " And T_TEMP.購入商品 < T_000.購入商品))=1);" strSQL = strSQL & " ) AS 商品1, " '順位が2のデータを強引に持ってくる strSQL = strSQL & " ( SELECT T_000.購入商品 FROM T_DATA AS T_000" strSQL = strSQL & " WHERE ((T_MOTO.顧客=T_000.顧客)" strSQL = strSQL & " AND ((Select Count(*) From T_DATA AS T_TEMP" strSQL = strSQL & " Where T_TEMP.顧客 = T_000.顧客" strSQL = strSQL & " And T_TEMP.購入商品 < T_000.購入商品))=2);" strSQL = strSQL & " ) AS 商品2 " strSQL = strSQL & " FROM T_DATA AS T_MOTO " strSQL = strSQL & " Group By 顧客; " Call RUN_SQL(strSQL) ' %> <hr> ↑なんとか顧客別に横に商品名を出せたのかなぁ・・でもなぁ<br> ここまでするなら、1レコード単位で回して、自分でやったほうがいいような・・<br> </body> </html> <% 'SQL文を受け取り、DBに対してSQLを発行、データを表示するテスト関数 Sub RUN_SQL(strSQL) 'ADO DB Connection オブジェクトを作成する、英文そのままじゃん Set db=Server.CreateObject("ADODB.Connection") 'データアクセスにはJet.OLEDB.4.0を使うことを設定 db.Provider = "Microsoft.Jet.OLEDB.4.0" '次に、接続DBの位置を渡すので、Server.MapPathで変換して渡す db.ConnectionString = Server.MapPath("db105.mdb") db.open 'データベースを開く Response.Write "発行するSQL文<br>" Response.Write Server.HTMLEncode(strSQL) & "<BR>" Set rs = db.Execute(strSQL) 'データの表示をテーブルで行う Response.Write "<TABLE Border='1'>" '見出しを(フィールド名を)そのまま書き込む Response.Write "<TR>" For Each fld_A In Rs.Fields 'フィールドのアイテムに対してループ Response.Write "<TH>" & fld_A.Name & "</TH>" '↑.Nameでフィールド名を表示する Next Response.Write "</TR>" 'お約束のEOFまでループは(データが無くなるまでループ)、 Do While rs.EOF = False 'レコードセットの.EOFがFalseの間 Response.Write "<TR>" '内容を表示する For Each fld_A In Rs.Fields 'フィールドのアイテムに対してループ Response.Write "<TD>" & fld_A.Value & "</TD>" '↑.Valueでフィールドの値を表示する Next Response.Write "</TR>" '次のレコードにポインタを移動する rs.MoveNext 'これを忘れると悲惨なことに、、、 Loop Response.Write "</TABLE>" 'テーブルは終わりです '後始末 rs.Close '開いていたレコードセットを閉じる db.Close 'データベースも閉じようよ Set db = Nothing 'お行儀よくオブジェクトも開放しましょう End Sub %>


Googleで関連項目を検索する

[キーワード:Count] と サンプル|解説|Dim|Set で検索
↓で検索して他のページも確認してみてください。

Google

あれ?私のページが検索に載ってない(笑)一度お試しを(私のページに、また戻ってきてね(爆))

[Topへ戻る] −−>[ASP系のTOPへ戻る] −−>[記事一覧を見る]

リンク 三流君 ASP系 の 解説を項目ごとにまとめてみました

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

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

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



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

その他:blogや広告↓
[日記的なblog]← あまり更新してないけど・・・
[永遠の下書き日記?]← 恥ずかしいのであまり載せてないけど・・・
[ひとりで掲示板?] ←一人で?掲示板?ぉぃぉぃ


[Topへ戻る] −−>[ASP系のTOPへ戻る] −−>[記事一覧を見る]

現在時刻は、2018/11/19 8:50:44です。