下記のフォームからメッセージを送ることができます。
＜SQL UNION演算子 で 表をつなげたレコードセットの作成＞
こんにちは、三流プログラマーのＫｅｎ３です。 今回は、小細工のオンパレードです。 ここまで小細工するなら、自分で計算させても・・と思いなおしたり（笑）/* * １．今回のキッカケ */
最近、小計・合計の表示プログラムにチャレンジしてます。 http://www.ken3.org/cgi-bin/test/test072-4.asp で、１レコード単位で読み込み、自分で集計しました。 http://www.ken3.org/cgi-bin/test/test073-3.asp では、マスターテーブルと明細テーブルをつなげて商品名を表示しました。 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_月別売上 と、演算フィールドを使用して、ＳＱＬ内で横系を計算させました。 今回は、小計にチャレンジしてみたいと思います。/* * ２．小計を取るには、グループ化で計算させる */
小計を計算するには、 Group By でレコードをグループ化して、 Sum(項目名)なんて感じの集計関数を使用する方法があります。 ---------------------------------------------------------------- ｜ 商品名 ｜ 4月 ｜ 5月 ｜ 6月 ｜ 7月 ｜ 8月 ｜ 9月 ｜ 上期計 ｜ ---------------------------------------------------------------- ｜ A1 ｜ 1 ｜ 2 ｜ 3 ｜ 4 ｜ 5 ｜ 6 ｜ 21 ｜ ｜ A2 ｜ 2 ｜ 0 ｜ 4 ｜ 5 ｜ 6 ｜ 7 ｜ 24 ｜ ｜ A3 ｜ 3 ｜ 1 ｜ 5 ｜ 6 ｜ 7 ｜ 8 ｜ 30 ｜ ---------------------------------------------------------------- ｜ 小計 ｜ 6 ｜ 3 ｜ 12 ｜ 15 ｜ 18 ｜ 21 ｜ 75 ｜ ---------------------------------------------------------------- 商品コードの頭１桁でグループ化して、４月〜９月の値を集計してみたいと思います。 Select Left([商品CD], 1) AS GroupCD , Sum([売上4]) , Sum([売上5]) , Sum([売上6]) , Sum([売上7]) , Sum([売上8]) , Sum([売上9]) >From T_月別売上 Group By Left([商品CD], 1) と、SQL文を作成してみた。 http://www.ken3.org/cgi-bin/test/test075-1.asp の実行結果は、 小計は GroupCD Expr1001 Expr1002 Expr1003 Expr1004 Expr1005 Expr1006 A 6 3 12 15 18 21 B 15 9 21 24 27 33 C 3 3 3 3 3 3 と、 項目名はASを指定してないので、自動的に振られているみたいだが、 なんとか、集計は出来たみたいです。 ※Sum([売上4]) , Sum([売上5]) , Sum([売上6]) Sum([売上4]) AS 小計4 , Sum([売上5]) AS 小計5 みたいに記述すると項目名が表示される。 <%@LANGUAGE=VBScript%> <html> <head> <title>商品CDの頭１桁目でグループ化して、小計を計算する</title> </head> <body> <h2>商品CDの頭１桁目でグループ化して、小計を計算する</h2> Select Left([商品CD], 1) AS GroupCD <br> , Sum([売上4]) , Sum([売上5]) , Sum([売上6])<br> , Sum([売上7]) , Sum([売上8]) , Sum([売上9])<br> From T_月別売上<br> Group By Left([商品CD], 1)<br> と、SQL文を作成してみた。 <hr> 小計は<br> <% '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 'データベースを開く '商品CDの一桁目を（左から１文字を）GroupCDにする 'Sum集計関数で項目の合計を計算する strSQL = "Select Left([商品CD], 1) AS GroupCD " strSQL = strSQL & ", Sum([売上4]) , Sum([売上5]) , Sum([売上6])" strSQL = strSQL & ", Sum([売上7]) , Sum([売上8]) , Sum([売上9])" strSQL = strSQL & " From T_月別売上" strSQL = strSQL & " Group By Left([商品CD], 1) " 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>/* * ３．２つの表を＋する、SQL UNION演算子 */
やりたいことは、 ---------------------------------------------------------------- ｜ 商品名 ｜ 4月 ｜ 5月 ｜ 6月 ｜ 7月 ｜ 8月 ｜ 9月 ｜ 上期計 ｜ ---------------------------------------------------------------- ｜ A1 ｜ 1 ｜ 2 ｜ 3 ｜ 4 ｜ 5 ｜ 6 ｜ 21 ｜ ｜ A2 ｜ 2 ｜ 0 ｜ 4 ｜ 5 ｜ 6 ｜ 7 ｜ 24 ｜ ｜ A3 ｜ 3 ｜ 1 ｜ 5 ｜ 6 ｜ 7 ｜ 8 ｜ 30 ｜ ---------------------------------------------------------------- ｜ 小計 ｜ 6 ｜ 3 ｜ 12 ｜ 15 ｜ 18 ｜ 21 ｜ 75 ｜ ---------------------------------------------------------------- ｜ B1 ｜ 4 ｜ 2 ｜ 6 ｜ 7 ｜ 8 ｜ 9 ｜ 36 ｜ ｜ B2 ｜ 5 ｜ 3 ｜ 7 ｜ 8 ｜ 9 ｜ 10 ｜ 42 ｜ ｜ B3 ｜ 6 ｜ 4 ｜ 8 ｜ 9 ｜ 10 ｜ 11 ｜ 48 ｜ ---------------------------------------------------------------- ｜ 小計 ｜ 15 ｜ 9 ｜ 21 ｜ 24 ｜ 27 ｜ 30 ｜ 126 ｜ と、小計は、普通は、間に入るでしょ・・・（入れなきゃ意味無いでしょ・・・） そっか、 Select Left([商品CD], 1) AS GroupCD , Sum([売上4]) , Sum([売上5]) , Sum([売上6]) , Sum([売上7]) , Sum([売上8]) , Sum([売上9]) From T_月別売上 Group By Left([商品CD], 1) で、計算したて下記の表示だけでは、ダメなんですね。 (小計の計算イメージ) GroupCD Expr1001 Expr1002 Expr1003 Expr1004 Expr1005 Expr1006 A 6 3 12 15 18 21 B 15 9 21 24 27 33 C 3 3 3 3 3 3 そこで、ＳＱＬであまり有名じゃない、私も今知った、 SQL文でUNIONって演算子が使えそうなんですよ。 UNION演算子？聞いたこと無いよ？ かすかにＵＮＩＯＮクエリーならAccessのMDBで聞いたことあるかなぁ UNION演算子を使うと、 ２つのクエリー（Select文の結果）を結合することが出来るんですよ はっ？何言ってんの？夢でも見てんじゃないの？ えっと、 Select * From A Union ALL Select * Form B と記述すると、ＡとＢを＋した結果のSelect文になるんですよ。 だから？何がしたいの？ Select * From 明細 Union ALL Select * From 小計 として、明細と小計の表を＋してみたいんですよ、私は。 あっそ、やってみれば？ なんか冷たいなぁ・・・できないと思っているダロ。黙って見てよ、やってみるか。 Select 商品CD,売上4,売上5,売上6,売上7,売上8,売上9 From T_月別売上 UNION ALL Select Left([商品CD], 1) AS GroupCD , Sum([売上4]) , Sum([売上5]) , Sum([売上6]) , Sum([売上7]) , Sum([売上8]) , Sum([売上9]) From T_月別売上 Group By Left([商品CD], 1) と、SQL文を作成してみた。 http://www.ken3.org/cgi-bin/test/test075-2.asp で、テスト実行すると、 商品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 B1 4 2 6 7 8 10 B2 5 3 7 8 9 11 B3 6 4 8 9 10 12 C1 1 1 1 1 1 1 C2 2 2 2 2 2 2 A 6 3 12 15 18 21 B 15 9 21 24 27 33 C 3 3 3 3 3 3 と、結果が返ってきた。 う〜ん、おしい。小計が下に集まってますね。 でも、UNION演算子で２つのSelect文の結果をまとめられました。 <%@LANGUAGE=VBScript%> <html> <head> <title>SQL UNION演算子で２つの表を結合する</title> </head> <body> <h2>SQL UNION演算子で２つの表を結合する</h2> <pre> Select 商品CD,売上4,売上5,売上6,売上7,売上8,売上9 From T_月別売上 <b>UNION ALL</b> Select Left([商品CD], 1) AS GroupCD , Sum([売上4]) , Sum([売上5]) , Sum([売上6]) , Sum([売上7]) , Sum([売上8]) , Sum([売上9]) From T_月別売上 Group By Left([商品CD], 1) </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 商品CD,売上4,売上5,売上6,売上7,売上8,売上9" strSQL = strSQL & " From T_月別売上" 'UNION ALL 演算子をはさむ。 strSQL = strSQL & " UNION ALL " 'Sum集計関数で項目の合計を計算する strSQL = strSQL & " Select Left([商品CD], 1) AS GroupCD " strSQL = strSQL & ", Sum([売上4]) , Sum([売上5]) , Sum([売上6])" strSQL = strSQL & ", Sum([売上7]) , Sum([売上8]) , Sum([売上9])" strSQL = strSQL & " From T_月別売上" strSQL = strSQL & " Group By Left([商品CD], 1) " 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>/* * ４.並べ替えの細工 UNION演算子で結合した表に対してOrder By してみる */
Select 商品CD,売上4,売上5,売上6,売上7,売上8,売上9 From T_月別売上 UNION ALL Select Left([商品CD], 1) AS GroupCD , Sum([売上4]) , Sum([売上5]) , Sum([売上6]) , Sum([売上7]) , Sum([売上8]) , Sum([売上9]) From T_月別売上 Group By Left([商品CD], 1) http://www.ken3.org/cgi-bin/test/test075-2.asp で、下記の表までなんとか表示できた。 商品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 B1 4 2 6 7 8 10 B2 5 3 7 8 9 11 B3 6 4 8 9 10 12 C1 1 1 1 1 1 1 C2 2 2 2 2 2 2 A 6 3 12 15 18 21 B 15 9 21 24 27 33 C 3 3 3 3 3 3 あとは、商品コードで並べ替えてなんとかならないかなぁ？ あっ、Left([商品CD], 1)のコードをLeft([商品CD], 1) & "ZZZZ" と、AにZZZZを付けて、AZZZZとしてから、 Order By 商品CDとやってみると、どうなるのだろう？ 下記のSQL文を作り、 UNION演算子で結合した表に対してOrder By 商品CDしてみました。 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 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 結果は、 http://www.ken3.org/cgi-bin/test/test075-3.asp を実行すると、 商品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 小計が、間に入ったイメージの表が出来ました。 あとは、商品名だよね。なんとか先が見えてきたかなぁ。 ソースはＳＱＬ部分が変わっただけですが、下記に載せときます。 <%@LANGUAGE=VBScript%> <html> <head> <title>SQL UNION演算子で結合した表に対してOrder By してみる</title> </head> <body> <h2>SQL UNION演算子で結合した表に対してOrder By してみる</h2> <pre> Select 商品CD,売上4,売上5,売上6,売上7,売上8,売上9 From T_月別売上 <b>UNION ALL</b> 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 </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 商品CD,売上4,売上5,売上6,売上7,売上8,売上9" strSQL = strSQL & " From T_月別売上" 'UNION ALL演算子をはさむ。 strSQL = strSQL & " UNION ALL" 'Sum集計関数で項目の合計を計算する strSQL = strSQL & " Select Left([商品CD], 1) & 'ZZZZ' AS GroupCD " strSQL = strSQL & ", Sum([売上4]) , Sum([売上5]) , Sum([売上6])" strSQL = strSQL & ", Sum([売上7]) , Sum([売上8]) , Sum([売上9])" strSQL = strSQL & " From T_月別売上" strSQL = strSQL & " Group By Left([商品CD], 1) & 'ZZZZ'" 'Order By 商品CD と商品コードで並べ替える strSQL = strSQL & " Order By 商品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>" 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>" Next Response.Write "</TR>" '次のレコードにポインタを移動する rs.MoveNext 'これを忘れると悲惨なことに、、、 Loop Response.Write "</TABLE>" 'テーブルは終わりです '後始末 rs.Close '開いていたレコードセットを閉じる db.Close 'データベースも閉じようよ Set db = Nothing 'お行儀よくオブジェクトも開放しましょう %> <hr> 終了です。<br> </body> </html>/* * ５．終わりの挨拶 ＜／ＨＴＭＬ＞ */
今回は、 Group By と Sumで集計を計算する方法 Select 商品CD,売上4,売上5,売上6,売上7,売上8,売上9 From T_月別売上 の明細データのSelect文 を UNION ALL で下記のグループ集計のSelect文をつなげて、 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 で、商品コード順にする。 なんか複雑なような、わかってしまえば、それなりに使えるような気もするし。 今回の解説でもＳＱＬ系まだまだなんだけど、 何かの参考となれば幸いです。 ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。
ここまで、読んでいただきどうもです。目的の情報が見つかったか？少々心配しつつ、、、
感想や質問・要望・苦情など 三流君へメッセージを送る。
下記のフォームからメッセージを送ることができます。
[三流君(TOP ken3.org へ戻る)]
/ [ASPで遊ぶ、失敗する]
/ [ASP記事 バックナンバー目次]
まぁ、基本はデータの受け取りかなぁ。
・[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記事 バックナンバー目次]