[三流君] −−> [ASPで遊ぶ、失敗する] −−> [バックナンバー一覧]
−−> No.076 小計・合計の表示、SQLで小細工の嵐(笑)

小計・合計の表示、SQLで小細工の嵐(笑)



本文(発行内容)


<小計・合計の表示、SQLで小細工の嵐(笑)>

こんにちは、三流プログラマーのKen3です。 今回は、小細工のまとめです(笑) 普通は、ここまでしないでしょ・・・と思いました(笑) ※ある意味、意地になってSQLを書きまくっているような・・・・

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

最近、小計・合計の表示プログラムにチャレンジしてます。 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ http://www.ken3.org/cgi-bin/test/test072-4.asp で、1レコード単位で読み込み、自分で集計処理しました。 ※なんて原始的な方法なのだろう・・・ ここから、一歩一歩、進化して?(And 退化して、余計なお世話的にハマる・・・) http://www.ken3.org/cgi-bin/test/test073-3.asp では、 Select * From T_月別売上, T_商品 Where T_月別売上.商品CD = T_商品.商品CD Order By T_月別売上.商品CD T_商品(マスター)とT_月別売上(明細テーブル)をつなげて商品名を表示しました。 http://www.ken3.org/cgi-bin/test/test074-2.asp では、 Select Left([商品CD], 1) AS GroupCD, T_月別売上.* ,([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計 ,([売上10]+[売上11]+[売上12]+[売上1]+[売上2]+[売上3]) AS 下期計 >From T_月別売上 と、演算フィールドを使用して、SQL内で横系を計算させました。 http://www.ken3.org/cgi-bin/test/test075-3.asp で、 Select 商品CD,売上4,売上5,売上6,売上7,売上8,売上9 From T_月別売上 UNION ALL Select Left([商品CD], 1) & 'ZZZZ' AS GroupCD , Sum([売上4]) , Sum([売上5]) , Sum([売上6]) , Sum([売上7]) , Sum([売上8]) , Sum([売上9]) From T_月別売上 Group By Left([商品CD], 1) & 'ZZZZ' Order By 商品CD と、UNION ALL演算子を使って、下記の表まで作りました。 商品CD 売上4 売上5 売上6 売上7 売上8 売上9 A1 1 2 3 4 5 6 A2 2 0 4 5 6 7 A3 3 1 5 6 7 8 AZZZZ 6 3 12 15 18 21 B1 4 2 6 7 8 10 B2 5 3 7 8 9 11 B3 6 4 8 9 10 12 BZZZZ 15 9 21 24 27 33 C1 1 1 1 1 1 1 C2 2 2 2 2 2 2 CZZZZ 3 3 3 3 3 3 今回は、まとめじゃないけど、 商品名と上期計の出力にチャレンジしてみます。

/* * 2.商品名の表示 */

商品名は、マスターとつなげて、もってくればいいんだっけ。 http://www.ken3.org/cgi-bin/test/test073-3.asp でやったみたいに、 Select * From T_月別売上, T_商品 Where T_月別売上.商品CD = T_商品.商品CD Order By T_月別売上.商品CD T_商品(マスター)とT_月別売上(明細テーブル)をつなげて 商品名を取得しますか。 Select 商品CD,商品名,売上4,売上5,売上6,売上7,売上8,売上9 From T_月別売上, T_商品 Where T_月別売上.商品CD = T_商品.商品CD UNION ALL Select Left([商品CD], 1) & 'ZZZZ' AS GroupCD , Sum([売上4]) , Sum([売上5]) , Sum([売上6]) , Sum([売上7]) , Sum([売上8]) , Sum([売上9]) From T_月別売上 Group By Left([商品CD], 1) & 'ZZZZ' Order By T_月別売上.商品CD おっと、これだと、 下の集計用のSQL文と列数が合わないか。 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Microsoft JET Database Engine エラー '80040e14' ユニオン クエリで選択した 2 つのテーブルまたはクエリの列数が一致しません。 /cgi-bin/test/test076-1.asp, 行 50 予想通り、エラーが出ると、ある意味気持ちいいね(ホントか?) ダミーで商品名を”小計”としてみますか。 Select T_月別売上.商品CD, T_商品.商品名, 売上4,売上5,売上6,売上7,売上8,売上9 From T_月別売上, T_商品 Where T_月別売上.商品CD = T_商品.商品CD UNION ALL Select Left([商品CD], 1) & 'ZZZZ' AS GroupCD , '小計' AS Dummy商品名 , Sum([売上4]) , Sum([売上5]) , Sum([売上6]) , Sum([売上7]) , Sum([売上8]) , Sum([売上9]) From T_月別売上 Group By Left([商品CD], 1) & 'ZZZZ' Order By T_月別売上.商品CD ポイントは、 ^^^^^^^^^^^^ '小計' AS Dummy商品名とSQL文に入れて、 列の数を合わせてみました。 ~~~~~~~~~~~~~~~~ よし、これでOKかなぁ。 あっ、マスターにデータが無い(エラーデータの) C1,C2は表示されなくていいんだけど、合計が表示されている・・・ う〜ん、グループ化は無条件にやっているからなぁ。 ↑Cが小計されている、エラーのイメージ。 Group化している表も、 マスターとつなげてOKのデータのみ集計しないとマズイみたいですね。 さらにSQLの表を変更します。 Select T_月別売上.商品CD, T_商品.商品名, 売上4,売上5,売上6,売上7,売上8,売上9 From T_月別売上, T_商品 Where T_月別売上.商品CD = T_商品.商品CD <b>UNION ALL</b> Select Left([T_月別売上.商品CD], 1) & 'ZZZZ' AS GroupCD , '小計' AS Dummy商品名 , Sum([売上4]) , Sum([売上5]) , Sum([売上6]) , Sum([売上7]) , Sum([売上8]) , Sum([売上9]) From T_月別売上, T_商品 Where T_月別売上.商品CD = T_商品.商品CD Group By Left([T_月別売上.商品CD], 1) & 'ZZZZ' Order By T_月別売上.商品CD と、修正してみました。 OKです、やっとマスターに無いグループCが消えました。 http://www.ken3.org/cgi-bin/test/test076-1.asp で、ここまでのテストが出来ます。 SQL文の作成は長いけど、下記のように作成しました。 'T_月別売上 から 商品コード、売上4〜9を取得する strSQL = "Select T_月別売上.商品CD, T_商品.商品名," strSQL = strSQL & " 売上4,売上5,売上6,売上7,売上8,売上9" strSQL = strSQL & " From T_月別売上, T_商品 " strSQL = strSQL & " Where T_月別売上.商品CD = T_商品.商品CD " 'UNION ALL演算子をはさむ。 strSQL = strSQL & " UNION ALL" 'Sum集計関数で項目の合計を計算する strSQL = strSQL & " Select Left([T_月別売上.商品CD], 1) & 'ZZZZ' AS GroupCD " strSQL = strSQL & ", '小計' AS Dummy商品名 " strSQL = strSQL & ", Sum([売上4]) , Sum([売上5]) , Sum([売上6])" strSQL = strSQL & ", Sum([売上7]) , Sum([売上8]) , Sum([売上9])" strSQL = strSQL & " From T_月別売上, T_商品 " strSQL = strSQL & " Where T_月別売上.商品CD = T_商品.商品CD " strSQL = strSQL & " Group By Left([T_月別売上.商品CD], 1) & 'ZZZZ'" 'Order By 商品CD と商品コードで並べ替える strSQL = strSQL & " Order By T_月別売上.商品CD" Set rs = db.Execute(strSQL) 'SQL発行、レコードセットの作成

/* * 3.上期計を追加する */

さてと、商品名を表示することが出来ました。 ここまでのSQLに上期計を追加してみたいと思います。 http://www.ken3.org/cgi-bin/test/test074-2.asp のテストで、 Select Left([商品CD], 1) AS GroupCD, T_月別売上.* ,([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計 ,([売上10]+[売上11]+[売上12]+[売上1]+[売上2]+[売上3]) AS 下期計 >From T_月別売上 と、演算フィールドを使用して、SQL内で横系(上期、下期)を計算させました。 これを参考に、SQL文に追加すると、 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Select T_月別売上.商品CD, T_商品.商品名, 売上4,売上5,売上6,売上7,売上8,売上9, ([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計 From T_月別売上, T_商品 Where T_月別売上.商品CD = T_商品.商品CD UNION ALL Select Left([T_月別売上.商品CD], 1) & 'ZZZZ' AS GroupCD , '小計' AS Dummy商品名 , Sum([売上4]) , Sum([売上5]) , Sum([売上6]) , Sum([売上7]) , Sum([売上8]) , Sum([売上9]) , Sum([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計 From T_月別売上, T_商品 Where T_月別売上.商品CD = T_商品.商品CD Group By Left([T_月別売上.商品CD], 1) & 'ZZZZ' Order By T_月別売上.商品CD となります。 http://www.ken3.org/cgi-bin/test/test076-2.asp で、テスト実行すると、なんとか集計されました。 下記がSQLの作成です(いいのか?こんなんで・・・) 'T_月別売上 から 商品コード、売上4〜9を取得する strSQL = "Select T_月別売上.商品CD, T_商品.商品名," strSQL = strSQL & " 売上4,売上5,売上6,売上7,売上8,売上9," strSQL = strSQL & "([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計" strSQL = strSQL & " From T_月別売上, T_商品 " strSQL = strSQL & " Where T_月別売上.商品CD = T_商品.商品CD " 'UNION ALL演算子をはさむ。 strSQL = strSQL & " UNION ALL" 'Sum集計関数で項目の合計を計算する strSQL = strSQL & " Select Left([T_月別売上.商品CD], 1) & 'ZZZZ' AS GroupCD " strSQL = strSQL & ", '小計' AS Dummy商品名 " strSQL = strSQL & ", Sum([売上4]) , Sum([売上5]) , Sum([売上6])" strSQL = strSQL & ", Sum([売上7]) , Sum([売上8]) , Sum([売上9])" strSQL = strSQL & ", Sum([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計" strSQL = strSQL & " From T_月別売上, T_商品 " strSQL = strSQL & " Where T_月別売上.商品CD = T_商品.商品CD " strSQL = strSQL & " Group By Left([T_月別売上.商品CD], 1) & 'ZZZZ'" 'Order By 商品CD と商品コードで並べ替える strSQL = strSQL & " Order By T_月別売上.商品CD" Set rs = db.Execute(strSQL) 'SQL発行、レコードセットの作成

/* * 4.総合計を+するタメさらにUNION演算子の使用 */

やれやれ・・・やっと完成したよ・・・ なんて一安心してたら、総合計が無いことに気が付く。 ※オイオイ、ツメが甘いよ 総合計?どうしましょう・・・ Group Byで全てのレコードを集計するの・・・ ソートもしないといけないので、 商品コードをZZZZZで集計してみますか。 そして、そのSelect文をUnion ALLでつなげるの? はい、その予定です・・・ Select T_月別売上.商品CD, T_商品.商品名, 売上4,売上5,売上6,売上7,売上8,売上9, ([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計 From T_月別売上, T_商品 Where T_月別売上.商品CD = T_商品.商品CD UNION ALL Select Left([T_月別売上.商品CD], 1) & 'ZZZZ' AS GroupCD , '小計' AS Dummy商品名 , Sum([売上4]) , Sum([売上5]) , Sum([売上6]) , Sum([売上7]) , Sum([売上8]) , Sum([売上9]) , Sum([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計 From T_月別売上, T_商品 Where T_月別売上.商品CD = T_商品.商品CD Group By Left([T_月別売上.商品CD], 1) & 'ZZZZ' UNION ALL Select 'ZZZZZ' AS GroupCD , '総合計' AS Dummy商品名 , Sum([売上4]) , Sum([売上5]) , Sum([売上6]) , Sum([売上7]) , Sum([売上8]) , Sum([売上9]) , Sum([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計 From T_月別売上, T_商品 Where T_月別売上.商品CD = T_商品.商品CD Group By 'ZZZZZ' Order By T_月別売上.商品CD と、SQL文を作成しました。 http://www.ken3.org/cgi-bin/test/test076-3.asp で、テストすると、おっ、それらしく表示されましたね。 ↑総合計が表示されたイメージです。 へぇ〜、Union ALLって、2つだけじゃなくて、3つもできましたね。 列数とタイプがあっていれば、いろいろと使えるのかも。 長いソースを下記に載せます。 <%@LANGUAGE=VBScript%> <html> <head> <title>総合計を計算して、Union ALL でつなげる・・・</title> </head> <body> <h2>総合計を計算して、Union ALL でつなげる・・・</h2> <pre> Select T_月別売上.商品CD, T_商品.商品名, 売上4,売上5,売上6,売上7,売上8,売上9, ([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計 From T_月別売上, T_商品 Where T_月別売上.商品CD = T_商品.商品CD <b>UNION ALL</b> Select Left([T_月別売上.商品CD], 1) & 'ZZZZ' AS GroupCD , '小計' AS Dummy商品名 , Sum([売上4]) , Sum([売上5]) , Sum([売上6]) , Sum([売上7]) , Sum([売上8]) , Sum([売上9]) , Sum([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計 From T_月別売上, T_商品 Where T_月別売上.商品CD = T_商品.商品CD Group By Left([T_月別売上.商品CD], 1) & 'ZZZZ' <b>UNION ALL</b> Select 'ZZZZZ' AS GroupCD , '総合計' AS Dummy商品名 , Sum([売上4]) , Sum([売上5]) , Sum([売上6]) , Sum([売上7]) , Sum([売上8]) , Sum([売上9]) , Sum([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計 From T_月別売上, T_商品 Where T_月別売上.商品CD = T_商品.商品CD Group By 'ZZZZZ' Order By T_月別売上.商品CD </pre> と、SQL文を作成してみた。 <hr> <% '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("db072.mdb") db.open 'データベースを開く 'T_月別売上 から 商品コード、売上4〜9を取得する strSQL = "Select T_月別売上.商品CD, T_商品.商品名," strSQL = strSQL & " 売上4,売上5,売上6,売上7,売上8,売上9," strSQL = strSQL & "([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計" strSQL = strSQL & " From T_月別売上, T_商品 " strSQL = strSQL & " Where T_月別売上.商品CD = T_商品.商品CD " 'UNION ALL演算子をはさむ。 strSQL = strSQL & " UNION ALL" 'Sum集計関数で項目の合計を計算する strSQL = strSQL & " Select Left([T_月別売上.商品CD], 1) & 'ZZZZ' AS GroupCD " strSQL = strSQL & ", '小計' AS Dummy商品名 " strSQL = strSQL & ", Sum([売上4]) , Sum([売上5]) , Sum([売上6])" strSQL = strSQL & ", Sum([売上7]) , Sum([売上8]) , Sum([売上9])" strSQL = strSQL & ", Sum([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計" strSQL = strSQL & " From T_月別売上, T_商品 " strSQL = strSQL & " Where T_月別売上.商品CD = T_商品.商品CD " strSQL = strSQL & " Group By Left([T_月別売上.商品CD], 1) & 'ZZZZ'" 'UNION ALL演算子をはさむ。 strSQL = strSQL & " UNION ALL" 'Sum集計関数で総合計を計算する strSQL = strSQL & " Select 'ZZZZZ' AS GroupCD " strSQL = strSQL & ", '総合計' AS Dummy商品名 " strSQL = strSQL & ", Sum([売上4]) , Sum([売上5]) , Sum([売上6])" strSQL = strSQL & ", Sum([売上7]) , Sum([売上8]) , Sum([売上9])" strSQL = strSQL & ", Sum([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計" strSQL = strSQL & " From T_月別売上, T_商品 " strSQL = strSQL & " Where T_月別売上.商品CD = T_商品.商品CD " strSQL = strSQL & " Group By 'ZZZZZ'" 'Order By 商品CD と商品コードで並べ替える strSQL = strSQL & " Order By T_月別売上.商品CD" Set rs = db.Execute(strSQL) 'SQL発行、レコードセットの作成 'データの表示をテーブルで行う 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 'お行儀よくオブジェクトも開放しましょう %> <hr> 終了です。<br> </body> </html>

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

今回は、 商品マスターから商品名を取得、 上期の合計(横計)の計算、 総合計を計算(縦計) するSQL文を+してみました。 http://www.ken3.org/cgi-bin/test/test076-3.asp で、テストできます。 フト考える・・・ ここまで大きなSQL文を作って、いろいろな小細工をしてみました。 なんか、出来なくは無かったけど、 はたして、これが正解なのか??? 大いに疑問が残りましたね。 みなさんの感想は? 普通に集計してもいいような気がするのは、私だけ? はじめに紹介した方法(RDB的な使い方をしていない、時代遅れの方法)、 http://www.ken3.org/cgi-bin/test/test072-4.asp で、テスト可能 , ソース載ってます。 商品マスターとつなげないのはイカンけど、 小計の計算や総合計は、自分で上から下に流れるループの中で計算してもいいのかなぁ そんな感じもするけど。 SQLなんか複雑なような、 わかってしまえば、それなりに使えるような気もするし。 みなさんは、 ケースによって使い分けてくださいね。 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ なにがなんでもSQLで処理しよう(SQL文一発で処理してやるぞ)、 複雑なSQLは作らない、自分でやるんだ(RDBなんて知らないよ) なんて、両極端にならないで、バランス良く使ってください。 読者の心の声:バランスいいサンプル最後に紹介しろ(作れよコラ!!) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ えっ、かんべんしてよ(本音はチョット疲れました(笑)) 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記事 バックナンバー目次]


広告: