[三流君] −−＞
[ＡＳＰで遊ぶ、失敗する] −−＞
[バックナンバー一覧]
−−＞ No.076 小計・合計の表示、SQLで小細工の嵐（笑）
小計・合計の表示、SQLで小細工の嵐（笑）
本文(発行内容)
＜小計・合計の表示、SQLで小細工の嵐（笑）＞
こんにちは、三流プログラマーのＫｅｎ３です。
今回は、小細工のまとめです（笑）
普通は、ここまでしないでしょ・・・と思いました（笑）
※ある意味、意地になってＳＱＬを書きまくっているような・・・・
/*
* １．今回のキッカケ
*/
最近、小計・合計の表示プログラムにチャレンジしてます。
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
http://www.ken3.org/cgi-bin/test/test072-4.asp
で、１レコード単位で読み込み、自分で集計処理しました。
※なんて原始的な方法なのだろう・・・
ここから、一歩一歩、進化して？（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_月別売上
と、演算フィールドを使用して、ＳＱＬ内で横系を計算させました。
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
今回は、まとめじゃないけど、
商品名と上期計の出力にチャレンジしてみます。
/*
* ２．商品名の表示
*/
商品名は、マスターとつなげて、もってくればいいんだっけ。
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
おっと、これだと、
下の集計用のＳＱＬ文と列数が合わないか。
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
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商品名とＳＱＬ文に入れて、
列の数を合わせてみました。
~~~~~~~~~~~~~~~~
よし、これでＯＫかなぁ。
あっ、マスターにデータが無い（エラーデータの）
C1,C2は表示されなくていいんだけど、合計が表示されている・・・
う〜ん、グループ化は無条件にやっているからなぁ。
↑Ｃが小計されている、エラーのイメージ。
Group化している表も、
マスターとつなげてＯＫのデータのみ集計しないとマズイみたいですね。
さらに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
と、修正してみました。
ＯＫです、やっとマスターに無いグループＣが消えました。
http://www.ken3.org/cgi-bin/test/test076-1.asp
で、ここまでのテストが出来ます。
ＳＱＬ文の作成は長いけど、下記のように作成しました。
'T_月別売上 から 商品コード、売上４〜９を取得する
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発行、レコードセットの作成
/*
* ３．上期計を追加する
*/
さてと、商品名を表示することが出来ました。
ここまでのＳＱＬに上期計を追加してみたいと思います。
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文に追加すると、
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
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
で、テスト実行すると、なんとか集計されました。
下記がＳＱＬの作成です（いいのか？こんなんで・・・）
'T_月別売上 から 商品コード、売上４〜９を取得する
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発行、レコードセットの作成
/*
* ４.総合計を＋するタメさらにUNION演算子の使用
*/
やれやれ・・・やっと完成したよ・・・
なんて一安心してたら、総合計が無いことに気が付く。
※オイオイ、ツメが甘いよ
総合計？どうしましょう・・・
Group Byで全てのレコードを集計するの・・・
ソートもしないといけないので、
商品コードをZZZZZで集計してみますか。
そして、そのＳｅｌｅｃｔ文を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って、２つだけじゃなくて、３つもできましたね。
列数とタイプがあっていれば、いろいろと使えるのかも。
長いソースを下記に載せます。
<%@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"
'次に、接続ＤＢの位置を渡すので、Server.MapPathで変換して渡す
db.ConnectionString = Server.MapPath("db072.mdb")
db.open 'データベースを開く
'T_月別売上 から 商品コード、売上４〜９を取得する
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>"
'お約束のＥＯＦまでループは(データが無くなるまでループ)、
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>
/*
* ５．終わりの挨拶 ＜／ＨＴＭＬ＞
*/
今回は、
商品マスターから商品名を取得、
上期の合計（横計）の計算、
総合計を計算（縦計）
するＳＱＬ文を＋してみました。
http://www.ken3.org/cgi-bin/test/test076-3.asp
で、テストできます。
フト考える・・・
ここまで大きなＳＱＬ文を作って、いろいろな小細工をしてみました。
なんか、出来なくは無かったけど、
はたして、これが正解なのか？？？
大いに疑問が残りましたね。
みなさんの感想は？
普通に集計してもいいような気がするのは、私だけ？
はじめに紹介した方法(RDB的な使い方をしていない、時代遅れの方法)、
http://www.ken3.org/cgi-bin/test/test072-4.asp
で、テスト可能 , ソース載ってます。
商品マスターとつなげないのはイカンけど、
小計の計算や総合計は、自分で上から下に流れるループの中で計算してもいいのかなぁ
そんな感じもするけど。
ＳＱＬなんか複雑なような、
わかってしまえば、それなりに使えるような気もするし。
みなさんは、
ケースによって使い分けてくださいね。
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
なにがなんでもＳＱＬで処理しよう(SQL文一発で処理してやるぞ)、
複雑なＳＱＬは作らない、自分でやるんだ(RDBなんて知らないよ)
なんて、両極端にならないで、バランス良く使ってください。
読者の心の声：バランスいいサンプル最後に紹介しろ（作れよコラ！！）
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
えっ、かんべんしてよ（本音はチョット疲れました（笑））
ＳＱＬ系は深いなぁと思いつつ、私の実力もまだまだと感じた小計処理でした。
何かの参考となれば幸いです。
ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。
ページフッター
ここまで、読んでいただきどうもです。目的の情報が見つかったか？少々心配しつつ、、、
三流君へ メッセージを送る
感想や質問・要望・苦情など 三流君へメッセージを送る。
下記のフォームからメッセージを送ることができます。
種類別のリンク や 広告など
[三流君(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が使えるので、あまり使用しないけど、普通のテキストファイル処理
・[テキストファイル処理]・・・ファイルを開いて、書き込む。１行読み込みなどを軽く
VBScriptでFormat関数が無いなど、微妙にVBAと違うけど
[VBScript関数関係の説明]・・・少し、処理を書いてみた。
[その他処理サンプル]・・・あまり良いサンプル作れなかったけど。。。
何かの参考となれば幸いです。
[三流君(TOP ken3.org へ戻る)]
/ [ASPで遊ぶ、失敗する]
/ [ASP記事 バックナンバー目次]
広告:
