none
データベースの値を降順で出力したい RRS feed

  • 質問

  • お世話になっております。
    一応自身で解決はしているのですが、釈然としないため質問させていただきます。
    開発環境はVB2008 ACCESS2010です。

    件名にあります通り、データベースの値を降順でExcelファイルへ出力するものとなっています。
    趣旨としては、顧客が 何の果物を、どの販売店で買ったか、を集計するものとなっています。(果物は一人1個

    他にもう少し賢いやり方があれば、ご教示願いたく思います。
    他に何かありそうな気がするのですが、無いような気もするし、という漠然とした状況で、
    どういう情報を元に、今回の件を調べれば分からなかったのでこちらで書かせていただきます。

    以下、概要及び、私が実施したことです。

    --------------------------------
    T_果物集計
    顧客ID
    果物CD
    果物名
    販売店(データ型はInt で、A=1 B=2 C=3 と定義する)
    --------------------------------

    というテーブルからSQLでCOUNT(顧客ID)を取得し、以下のようなレイアウトにて
    果物名の総合計値を降順にて出力します。

    --------------------------------
              列
           |総合計|販売店A|  B |  C |
    果物名1    10     6     3    1
    果物名2     5     2     2    1
    果物名3     2     0     0    2
    --------------------------------

    この際、VB側で、下記SQLを生成しDataAdapterにて接続したデータテーブルから値を集計し、
    あらかじめ用意してある、Cols().Rows()という配列へ格納しています。

    --------------------------------
    SQL
    '果物売上取得(降順用)
         sSQL = "SELECT 果物_集計.果物コード,果物_集計.果物名,COUNT(果物_集計.顧客ID) AS 件数, FROM 果物_集計 "
         sSQL &= " GROUP BY 果物_集計.果物,果物名"
         sSQL &= " ORDER BY COUNT(果物_集計.顧客ID) DESC;"

    '果物売上取得(販売店用)
         sSQL = "SELECT 果物_集計.販売店,果物コード,果物_集計.果物名,COUNT(果物_集計.顧客ID) AS 件数, FROM 果物_集計 "
         sSQL &= " GROUP BY 果物_集計.販売店,果物コード,果物名"
    --------------------------------

    やったこととしては、まず、どの果物が一番売れたかを降順で出力するためのSQLから値を取得します。
    その後、Cols(0).Rows().RowName というプロパティ値へ.Item("果物名")を入れます。
    Rows(1).RosName = バナナ
    Rows(2).RosName = リンゴ
    Rows(3).RosName = スイカ

    その後、このような感じで一度果物名を降順にてエクセルへ出力します。

                    For i = 1 To UBound(Cols(0).Rows)
                        'エクセル行生成
                        xlSheet.Range("A" & iRow, "V" & iRow).Insert()
                        xlSheet.Cells(iRow, 1).Value = Cols(0).Rows(i).RowName
                        iRow = iRow + 1
                    Next

    次に、取得した配列の、Rows.RowNameの値のみを残し、他のデータを削除し、
    Cols(1)~(3)のRows.RowNameへ、Cols(0)で取得したRowNameを再定義しなおします。

    そして、販売店用のSQLより再度データを取得し、(こちらでは販売店も集計条件としています)
    Cols(1)=1(販売店A)
    Cols(2)=2(販売店B)
    Cols(3)=3(販売店C)   とし、
    一致する販売店の、Rows.RowNameが一致する箇所へ
    Cols().Rows().Countというプロパティ値へデータを積み上げていきます。
    (販売店Aでリンゴがいくつ売れましたー)

    そして、各販売店でどの果物が売れたか積み上げていったデータをエクセルへ出力します。

    以上です、どうぞよろしくお願いします。





    2011年6月14日 3:17

回答

  • クロス集計すれば良いと思います。SQLは以下のようになると思います。未検証です。

    TRANSFORM COUNT(T_果物集計.顧客ID) AS 数
    SELECT T_果物集計.果物名, COUNT(T_果物集計.顧客ID) AS 合計数
    FROM T_果物集計
    GROUP BY T_果物集計.果物名
    PIVOT T_果物集計.販売店

     


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    • 回答としてマーク y_usiro 2011年6月14日 22:02
    2011年6月14日 5:12
    モデレータ

すべての返信

  • クロス集計すれば良いと思います。SQLは以下のようになると思います。未検証です。

    TRANSFORM COUNT(T_果物集計.顧客ID) AS 数
    SELECT T_果物集計.果物名, COUNT(T_果物集計.顧客ID) AS 合計数
    FROM T_果物集計
    GROUP BY T_果物集計.果物名
    PIVOT T_果物集計.販売店

     


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    • 回答としてマーク y_usiro 2011年6月14日 22:02
    2011年6月14日 5:12
    モデレータ
  • trapemiyaさま

    ご返信ありがとうございます。
    クロス集計! 単語のみの知識しかなく、実際どのようなものか分からなかったのですが、
    この機会にちゃんと調べたところ、とても便利な機能ですね。
    確かに、今回の件のような場合だと、クロス集計にて得たクエリからデータを収集するのが一番良さそうです!

    今回、一つ漏れがあり、出力した出力値の降順以外に、同じ件数に関しては果物CDの値が若い順に並べる処理が必要でした。
    ORDER BYで、果物CD ASCは出来たのですが、COUNT(T_果物集計.顧客ID)をDESCすることが出来なかったため、
    一度作成したクエリに、もう一度SELECTを実行することで、一番望む形の表データを得ることが出来ました。

    この度はご回答ありがとうございました。 

    2011年6月14日 22:08