[三流君] −−＞
[ＡＳＰで遊ぶ、失敗する] −−＞
[バックナンバー一覧]
−−＞ No.075 SQL UNION演算子 で 表をつなげたレコードセットの作成
SQL UNION演算子 で 表をつなげたレコードセットの作成
本文(発行内容)
＜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>
-【けんぞう！】---------------------------------------------------------
月500円、タバコなら２箱、120円缶コーヒーなら４缶分の謝礼をＧｅｔするなら
http://www.ken3.org/etc/500yen/ ←無料アンケート系の広告です。
『チッ、がんばって回答して月500円かよ』（お馬鹿なプログラマー：31歳）
------------------------------------------------------------------------
/*
* ５．終わりの挨拶 ＜／ＨＴＭＬ＞
*/
今回は、
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
で、商品コード順にする。
なんか複雑なような、わかってしまえば、それなりに使えるような気もするし。
今回の解説でもＳＱＬ系まだまだなんだけど、
何かの参考となれば幸いです。
ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。
フィードバック
ASP系の→[掲示板]←を覗く、質問を書き込む
評価・感想
三流君の主なリンク先
[アクセスランキング]
[サイトマップ]
[リンク先・相互リンク先など]
Ken3の日記(weblog) --
[広告・副収入系]
[プログラマー業務の愚痴]
[VBA系の話題]
[ASP系の話題]
[コンビニ系ネタ]
[その他]
その他 宣伝広告