三流君ASPで遊ぶ、失敗する
バックナンバー No.75 〜 No.79
＜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
で、商品コード順にする。
なんか複雑なような、わかってしまえば、それなりに使えるような気もするし。
今回の解説でもＳＱＬ系まだまだなんだけど、
何かの参考となれば幸いです。
ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。
＜小計・合計の表示、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>
-【けんぞう！】---------------------------------------------------------
ASPが利用可能なレンタルサーバーをお探しのアナタ、
http://www.ken3.org/asp/server.html ← けんぞうも使っているサーバーの紹介
『おっIISでbasp21でメール送信、mdbも使えるよ』（三流ＰＧ：31歳）
------------------------------------------------------------------------
/*
* ５．終わりの挨拶 ＜／ＨＴＭＬ＞
*/
今回は、
商品マスターから商品名を取得、
上期の合計（横計）の計算、
総合計を計算（縦計）
するＳＱＬ文を＋してみました。
http://www.ken3.org/cgi-bin/test/test076-3.asp
で、テストできます。
フト考える・・・
ここまで大きなＳＱＬ文を作って、いろいろな小細工をしてみました。
なんか、出来なくは無かったけど、
はたして、これが正解なのか？？？
大いに疑問が残りましたね。
みなさんの感想は？
普通に集計してもいいような気がするのは、私だけ？
はじめに紹介した方法(RDB的な使い方をしていない、時代遅れの方法)、
http://www.ken3.org/cgi-bin/test/test072-4.asp
で、テスト可能 , ソース載ってます。
商品マスターとつなげないのはイカンけど、
小計の計算や総合計は、自分で上から下に流れるループの中で計算してもいいのかなぁ
そんな感じもするけど。
ＳＱＬなんか複雑なような、
わかってしまえば、それなりに使えるような気もするし。
みなさんは、
ケースによって使い分けてくださいね。
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
なにがなんでもＳＱＬで処理しよう(SQL文一発で処理してやるぞ)、
複雑なＳＱＬは作らない、自分でやるんだ(RDBなんて知らないよ)
なんて、両極端にならないで、バランス良く使ってください。
読者の心の声：バランスいいサンプル最後に紹介しろ（作れよコラ！！）
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
えっ、かんべんしてよ（本音はチョット疲れました（笑））
ＳＱＬ系は深いなぁと思いつつ、私の実力もまだまだと感じた小計処理でした。
何かの参考となれば幸いです。
ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。
＜SQL AS演算子で別名管理＞
こんにちは、三流プログラマーのＫｅｎ３です。
/*
* １．今回のキッカケ
*/
掲示板に下記の質問をもらいました
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>投稿者 ： SQL初心者
>タイトル ： 0 AS やNull ASについて
>
>タイトルのことなんですが、SQLのSELECT文で"0 AS"や"Null AS"と
>使われていることがありますが、アレは何なのでしょうか？
>使い方を調べ見ましたが、いまいちです。
>
>使っているのはよく見るのですが。
>ご存知でしたら教えてください!
-------
単純な解答だと、ＡＳ演算子は別名を定義してます。
なんて本人もわけのわからない解答だったりします。
※実際のＳＱＬ文を載せてもらえると、説明しやすいけど、
メルマガのネタにSQL文を作ってみました。
/*
* ２．Null AS を使う場面を考えてみる。
*/
ＡＳ演算子は別名を定義してます。
Null AS かぁ・・・
項目をＮｕｌｌにしたい・・・う〜ん。
Whereの条件で項目 IS Nullは見かけるけどなぁ。
あっ、前回、UNIONでSELECT文をつなげた時、
列数が合わないと、エラーが発生してました。
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商品名とＳＱＬ文に入れて、
列の数を合わせてました。
~~~~~~~~~~~~~~~~
これを、
Null AS Dummy商品名とＳＱＬ文に入れて、
空白データを作成してみます。
http://www.ken3.org/cgi-bin/test/test077-1.asp
で、
UNIONで表をつなげる時、列数が合わないとエラーになるので、
Null AS DUMMY商品名とダミーの項目を作り列数を合わせる
Select T_月別売上.商品CD, T_商品.商品名,
売上4,売上5,売上6,売上7,売上8,売上9
From T_月別売上, T_商品
Where T_月別売上.商品CD = T_商品.商品CD
UNION ALL
Select Left([T_月別売上.商品CD], 1) & 'ZZZZ' AS GroupCD
, Null 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
と、SQL文を作成してみた。
/*
* ３．ＡＳ演算子は別名を定義してます。
*/
FFFFF As XXXXX
と、フィールド名やテーブル名を違う名前として使用可能にするために、
AS演算子を使用してます。
T_月別売上テーブル
^^^^^^^^^^^^^^^^^^
商品CD
売上4
売上5
・
・
売上12
売上1
売上2
売上3
とテーブル、フィールドがあったら、
↑作成したテーブルイメージ
ＳＱＬ文を作成して、テストしてみる。
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Select 商品CD,売上4,売上5,売上6
From T_月別売上テーブル
とか、ＳＱＬを書くのですが、
AS 演算子を使用して別名のテストをしてみます。
~~~~~~~~~
Select 商品CD, 売上4 AS ４月売上, DATA.売上5, DATA.売上6 AS ６月売上
From T_月別売上テーブル AS DATA
--
売上4 AS ４月売上
DATA.売上5
DATA.売上6 AS ６月売上
T_月別売上テーブル AS DATA
としてみました、結果は？
http://www.ken3.org/cgi-bin/test/test077-2.asp
でテスト実行すると、
商品CD ４月売上 売上5 ６月売上
A1 1 2 3
A2 2 0 4
なんて感じでした。
/*
* ４. 0 AS XXXX, 1 AS YYYY, 2 AS ZZZZは？
*/
なるほどね・・ＡＳは別名ですか。
Null AS XXXX で、項目作ったりできるのね。
だとすると、
0 AS XXXXは、ゼロと数字を作るのかい？
なんかイヤな予感・・・するんだけど。
じゃ、やってみれは？
Select 0 AS XXXX, 1 AS YYYY, 2 AS ZZZZ
From T_月別売上テーブル
を実行すると、結果は？
http://www.ken3.org/cgi-bin/test/test077-3.asp
を実行すると、
XXXX YYYY ZZZZ
0 1 2
0 1 2
あらら、出来ちゃいましたね。
-【けんぞう！】---------------------------------------------------------
ASPが利用可能なレンタルサーバーをお探しのアナタ、
http://www.ken3.org/asp/server.html ← けんぞうも使っているサーバーの紹介
『おっIISでbasp21でメール送信、mdbも使えるよ』（三流ＰＧ：31歳）
------------------------------------------------------------------------
/*
* ５．終わりの挨拶 ＜／ＨＴＭＬ＞
*/
今回は、
ＡＳ 演算子で、別名を作ることを少し解説しました。
Web上を検索してみると、
NULL asの別のサンプルは、
>SELECT title,author FROM text.clob_books b,
>TABLE(db2ext.textsearch('"ガーデニング"','TEXT','IX_AUTHOR',
>0,10,CAST(NULL as char))) T WHERE T.primKey = B.id
と
CAST(NULL as char)
キャスト？NULL as char？キャラにしてる？
0 asの別のサンプルは、
>CREATE FUNCTION NotNull(text) RETURNS int4
>AS ' (SELECT 1 as RESULT where $1 is not NULL) union
> (SELECT 0 as RESULT where $1 is NULL)
> ' LANGUAGE 'sql';
なんか、単なるＡＳで別名・・じゃない使い方のような・・・
ＳＱＬ系は深いなぁと思いつつ、
なんかキレの無いサンプルだったなぁ・・・
私の実力もまだまだと感じたＡＳ演算子のテスト処理でした。
何かの参考となれば幸いです。
ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。
＜Server.URLEnCodeで文字列をエンコードする＞
こんにちは、三流プログラマーのＫｅｎ３です。
今回は、
あまり使わないんだけど、
文字列のエンコードの関数を使ってみます。
/*
* １．今回のキッカケ
*/
よく使っている、検索サービスなどで、
三流君を検索すると、
http://websearch.yahoo.co.jp/bin/query?p=%bb%b0%ce%ae%b7%af
なんて、
%XXと漢字などが（+や%スペースなども）
エンコードされてＵＲＬに乗せて、パラメータを渡している、
そんな場面を見かけると思います。
<FORM ACTION="test061-1.asp" METHOD="GET">
調査したい文字列を入力してください。<br>
<INPUT TYPE="text" SIZE="30" NAME="DATA" VALUE="<%=CHK_DATA%>">
<INPUT TYPE="submit" VALUE="コード調査開始"><br>
↑いろいろと遊んでみてください↑
</FORM>
なんて感じで、HTMLのフォームからGET指定で渡すと、
URL付でtest061-1.aspが呼ばれますよね。
-- 枠外・余談 --
ホントのキッカケは、
http://www.ken3.org/etc/sake.html
日本酒・焼酎の販売で、楽天に飛ばす商品検索のボタンを作りたくて
調べてました。（パラメータとして、商品名を渡したくて）
/*
* ２．簡単なサンプル
*/
フォームから受け取った値を、
Server.URLEnCodeを使い、エンコードして表示するサンプルを作ってみます。
<FORM ACTION="test078-1.asp" METHOD="POST">
調査したい文字列を入力してください。<br>
<INPUT TYPE="text" SIZE="30" NAME="DATA" VALUE="<%=CHK_DATA%>">
<INPUT TYPE="submit" VALUE="Server.URLEnCode 開始"><br>
↑いろいろと遊んでみてください↑
</FORM>
と、FORMでPOSTでデータを送り、
CHK_DATA = Request.Form("DATA") 'パラメータの代入
なんて感じで、データを受け取って、
データありの時、
'長さをチェックする
If Len(CHK_DATA) <> 0 Then '文字が入っていたら
Response.Write "受け取ったデータは["
Response.Write Server.HTMLEncode(CHK_DATA) & "]です<br>"
'*エンコードした結果を表示する
Response.Write "Server.URLEnCodeでエンコードした結果は、<BR>[<b>"
Response.Write Server.URLEnCode(CHK_DATA) & "</b>]です<BR>" & vbCRLF
End If
単純に、
Server.URLEnCode(CHK_DATA)
と変換して、画面に表示してます。
http://www.ken3.org/cgi-bin/test/test078-1.asp
でテスト可能なので、いろいろと遊んでみてください。
test078-1.asp ソース↓
<%@LANGUAGE=VBScript%>
<html>
<head>
<title>Server.URLEnCodeを使い、エンコードして表示する</title>
</head>
<body>
<h1>Server.URLEnCodeを使い、エンコードして表示する</h1>
<!-- Ken3 --><!-- #include file="info.inc" -->
<br>
Server.URLEnCode(文字列)でエンコードしただけです（笑）<br>
※詳細は、ソースと詳細解説のページを見てください。<br>
<hr>
<%
CHK_DATA = Request.Form("DATA") 'パラメータの代入
'長さをチェックする
If Len(CHK_DATA) <> 0 Then '文字が入っていたら
Response.Write "受け取ったデータは["
Response.Write Server.HTMLEncode(CHK_DATA) & "]です<br>"
'*エンコードした結果を表示する
Response.Write "Server.URLEnCodeでエンコードした結果は、<BR>[<b>" & vbCRLF
Response.Write Server.URLEnCode(CHK_DATA) & "</b>]です<BR>" & vbCRLF
End If
%>
<HR>
<FORM ACTION="test078-1.asp" METHOD="POST">
調査したい文字列を入力してください。<br>
<INPUT TYPE="text" SIZE="30" NAME="DATA" VALUE="<%=CHK_DATA%>">
<INPUT TYPE="submit" VALUE="Server.URLEnCode 開始"><br>
↑いろいろと遊んでみてください↑
</FORM>
<br>
</body>
</html>
/*
* ３．パラメータ付で検索画面を呼んでみる
*/
なかなか、良いサンプル思いつかなかったのですが、
Server.URLEnCode
^^^^^^^^^^^^^^^^^
のサンプルを他店や本で見ると、やはり、yahooやGoogleの検索が多いので、
キーワードが入力されたら、
左にGoogle,右にMSNの検索結果を表示してみたいと思います。
※yahooとGoogleの検索結果はほぼ一緒なので・・・
HTMLのフレームって、なんか検索関係に嫌われていると噂があるので、
最近、みかけなくなってきました。
※なんて言っている私も、フレーム廃止してますが。
昔私が使っていたフレーム処理だと、
バックナンバーで上に目次のタイトル・内容、
下に飛べるように目次のショートカットなんて処理を書いてました。
http://www.ken3.org/asp/backno-asp.html ←バックナンバーのページ
のソース↓
<html>
<HEAD>
<TITLE>三流君 ＡＳＰで遊ぶ、失敗する</TITLE>
<META NAME="keyword" content="プログラミング, ASP, VBScript, IIS, HTML">
<META NAME="discription" content="ASPのサンプル、失敗談など">
</HEAD>
<frameset rows="*,60" FRAMEBORDER=1 BORDER=1 FRAMESPACING=0>
<frame SRC="http://www.ken3.org/backno/backno_asp_mokuji.html" NAME="F_RIGHT">
<frame SRC="http://www.ken3.org/cgi-bin/bmenu/backno_asp_menu.asp" NAME="F_BMENU">
</frameset>
</html>
こんな感じで、フレームの分割をしてました。
<frameset rows="*,60" FRAMEBORDER=1 BORDER=1 FRAMESPACING=0>
これは、上下分割なので、
今回、左右で半分半分の分割にしたいので、
<frameset cols="50%,50%" FRAMEBORDER=1 BORDER=1 FRAMESPACING=0>
にして、フレームのソースを
<frame SRC="グーグルのURL?Q=XXXX" NAME="f_google">
<frame SRC="ＭＳＮのＵＲＬ?Q=XXX" NAME="f_MSN">
としてみます。
三流君[%8EO%97%AC%8CN]を検索する場合
http://www.google.com/search?hl=ja&ie=Shift_JIS&q=%8EO%97%AC%8CN
と
http://search.msn.co.jp/results.asp?CY=ja&cp=932&q=%8EO%97%AC%8CN
のパラメータで検索してみます。
処理のポイントは、特に無く（笑）
Server.URLEnCodeで、エンコードした検索用のキーワードを渡しています。
http://www.ken3.org/cgi-bin/test/test078-2.asp
でテスト可能なので、
右にGoogle、左にYahooの検索で、遊んでみてください。
※なんか、違和感あるけど。
<%@LANGUAGE=VBScript%>
<html>
<%
CHK_DATA = Request.Form("keyword") 'パラメータの代入
'長さをチェックする
If Len(CHK_DATA) <> 0 Then '文字が入っていたら
strQ = Server.URLEnCode(CHK_DATA) '検索文字列をエンコードする
'フレームを作成する
Response.Write "<frameset cols='50%,50%' FRAMEBORDER=1 BORDER=1>"
Response.Write vbCRLF '改行を出力
'左のフレームはGoogle
Response.Write "<frame SRC='"
Response.Write "http://www.google.com/search?hl=ja&ie=Shift_JIS&"
Response.Write "q=" & strQ
Response.Write "' NAME='f_google'>"
Response.Write vbCRLF '改行を出力
'右のフレームはMSN
Response.Write "<frame SRC='"
Response.Write "http://search.msn.co.jp/results.asp?CY=ja&cp=932&"
Response.Write "q=" & strQ
Response.Write "' NAME='f_MSN'>"
Response.Write vbCRLF '改行を出力
'フレームを閉じる
Response.Write "</frameset>"
Else '↓入力用のフォームを表示する(Keyワードが未入力の時)
%>
<head>
<title>Server.URLEnCodeを使い、検索パラメータを作る</title>
</head>
<body>
<br>
<h1>Server.URLEnCodeを使い、検索パラメータを作る</h1>
<HR>
好きなキーワードを入れて、検索開始ボタンを押してください<br>
<FORM ACTION="test078-2.asp" METHOD="POST">
検索したいキーワードを入力してください。<br>
<INPUT TYPE="text" SIZE="30" NAME="keyword" VALUE="<%=CHK_DATA%>">
<INPUT TYPE="submit" VALUE="検索開始"><br>
↑いろいろと遊んでみてください↑
</FORM>
<br>
<!-- Ken3 --><% Call OUT_SRC("Server.URLEnCode") 'ソースの表示関数を呼ぶ %>
<!-- Ken3 --><!-- #include file="inc_mokuji.inc" -->
</body>
<%
End If
%>
</html>
-【けんぞう！】---------------------------------------------------------
ASPが利用可能なレンタルサーバーをお探しのアナタ、
http://www.ken3.org/asp/server.html ← けんぞうも使っているサーバーの紹介
『おっIISでbasp21でメール送信、mdbも使えるよ』（三流ＰＧ：31歳）
------------------------------------------------------------------------
/*
* ４．終わりの挨拶 ＜／ＨＴＭＬ＞
*/
今回は、
Server.URLEnCode
を使って、エンコードしたパラメータを作成してみました。
右にGoogle、左にYahooの違和感ある検索で、遊んでみてください。
http://www.ken3.org/cgi-bin/test/test078-2.asp
で検索テストできます。
もっと違う使い方、あるとは思いますが、一例だと思ってください。
何かの参考となれば幸いです。
ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。
＜文字列9-30をExcelに移すと日付と誤解される・・・＞
こんにちは、三流プログラマーのＫｅｎ３です。
今回は、
9-30とかの文字列として扱いたいデータが、
ASPからExcelへ持って行くと、
親切にじゃなくって、勝手に2003/09/30と認識される、そんな話です。
/*
* １．今回のキッカケ
*/
メールで下記の質問をもらいました。
------
>今回の質問はASPでデーターベースのデーターを検索しIEで表示したデーターを
>excelに取り込んだところ，ある文字列のデーターの中に"97-12"や"03-2-17"など
>が日付データー変わってしまたので，どうしたら，WEBの表示のままでexcelに
>はいるでしょうか
------
ん？
何のことだろう？
テストで、簡単なテーブルを表示してみる。
<html>
<body>
<table border=1>
<tr><td>code</td><td>DATA</td></tr>
<tr><td>b1</td><td>9-30</td></tr>
<tr><td>d1</td><td>10-01</td></tr>
<tr><td>f1</td><td>2003-10-02</td></tr>
</table>
</body>
</html>
これを、コピーして、Excelへ貼り付けると。
あらら・・・9-30が勝手に2003/09/30と認識されたよ。
形式を選択してで、文字列で貼り付けると。
b1 9-30とセルがつながるし、う〜ん。。。
/*
* ２．Excelの名前を付けて保存でＨＴＭＬ形式にしてみたら・・・
*/
<TD>9-30</TD>
だと、Excelへコピーしたりすると、日付に変わってしまいます。
Web-->Excelで、いろいろと探してました。
けど、全然、わかりませんでした。(オイオイ)
少し、頭を切り替えて
（なんて言いつつ、別のVBA系質問でシートのhtml保存で気が付いたんだけど）
Excelでセルを文字型にして、
9-30,10-1,2003-10-02を入力して、
そのデータを名前を付けて保存の機能を使い、
htmlファイルに保存しました（ｈｔｍｌファイルを作成しました。）
すると、
<td class=xl2217744>9-30</td>
<td class=xl2217744>10-1</td>
<td class=xl2217744>2003-10-02</td>
と、TDタグでclassを定義しているみたいです。
このデータをコピーすると（このＨＴＭＬをコピーすると）
9-30のままで、Excelにコピーされます。
classを探らないといけませんが、
日付型の項目、これでかわせそうです。
下記、Excel君が名前を付けて保存で作成してくれた、HTMLファイルです。
※意味不明だけど・・・
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=shift_jis">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 10">
<link rel=File-List href="Book1.files/filelist.xml">
<style id="Book1_17744_Styles">
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
.font517744
{color:windowtext;
font-size:6.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:"ＭＳ Ｐゴシック", monospace;
mso-font-charset:128;}
.xl1517744
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:windowtext;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:"ＭＳ Ｐゴシック", monospace;
mso-font-charset:128;
mso-number-format:General;
text-align:general;
vertical-align:middle;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
.xl2217744
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:windowtext;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:"ＭＳ Ｐゴシック", monospace;
mso-font-charset:128;
mso-number-format:"\@";
text-align:general;
vertical-align:middle;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
ruby
{ruby-align:left;}
rt
{color:windowtext;
font-size:6.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:"ＭＳ Ｐゴシック", monospace;
mso-font-charset:128;
mso-char-type:katakana;}
-->
</style>
</head>
<body>
<!--[if !excel]> <![endif]-->
<!--以下の情報は、Excel の Web ページとして発行 ウィザードで生成されました。-->
<!--同じアイテムが Excel から再発行されるとき、DIV タグ間のすべての情報が置き換えられます。-->
<!----------------------------->
<!--Excel の Web ページとして発行 ウィザードのアウトプットの始まり-->
<!----------------------------->
<div id="Book1_17744" align=center x:publishsource="Excel">
<table x:str border=0 cellpadding=0 cellspacing=0 width=165 style='border-collapse:
collapse;table-layout:fixed;width:124pt'>
<col width=72 style='width:54pt'>
<col width=93 style='mso-width-source:userset;mso-width-alt:2976;width:70pt'>
<tr height=18 style='height:13.5pt'>
<td height=18 class=xl1517744 width=72 style='height:13.5pt;width:54pt'>CODE</td>
<td class=xl2217744 width=93 style='width:70pt'>DATA</td>
</tr>
<tr height=18 style='height:13.5pt'>
<td height=18 class=xl1517744 style='height:13.5pt'>b1</td>
<td class=xl2217744>9-30</td>
</tr>
<tr height=18 style='height:13.5pt'>
<td height=18 class=xl1517744 style='height:13.5pt'>d1</td>
<td class=xl2217744>10-1</td>
</tr>
<tr height=18 style='height:13.5pt'>
<td height=18 class=xl1517744 style='height:13.5pt'>f1</td>
<td class=xl2217744>2003-10-02</td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=72 style='width:54pt'></td>
<td width=93 style='width:70pt'></td>
</tr>
<![endif]>
</table>
</div>
<!----------------------------->
<!--Excel の Web ページとして発行 ウィザードのアウトプットの終わり-->
<!----------------------------->
</body>
</html>
/*
* ３．長いのヤダな、、style='mso-number-format:"\@";' を指定する
*/
でも、日付への変換を防ぐために、あんなに.Classを定義しないといけないの？
う〜ん、あまり使いたくないですよね。
プロパティじゃないけど、
<td class=xl2217744>9-30</td>
<td class=xl2217744>10-1</td>
<td class=xl2217744>2003-10-02</td>
のclass=xl2217744を見ると、
.xl2217744
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:windowtext;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:"ＭＳ Ｐゴシック", monospace;
mso-font-charset:128;
mso-number-format:"\@";
text-align:general;
vertical-align:middle;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
なんて感じで定義されています。
フォントとかイロイロとありますが、
怪しいのは、
mso-number-format:"\@";
これって、なんか怪しくないですか？
HTMLのスタイルシートのページを見ていると、
クラスはスタイルの集まりみたいなことを書いてあります。
サンプルを見たりすると、
<TD style='XXXXXX'>
なんて書いてある書き方も発見しました。
よし、チョット遊んでみるか？と思い、下記のＨＴＭＬを作成しました。
<html>
<body>
<table border=1>
<tr><td>code</td><td>DATA</td></tr>
<tr><td>a1</td><td style='mso-number-format:"\@";'>9-30</td></tr>
<tr><td>b1</td><td>9-30</td></tr>
<tr><td>c1</td><td style='mso-number-format:"\@";'>10-01</td></tr>
<tr><td>d1</td><td>10-01</td></tr>
</table>
</body>
</html>
このデータをコピーすると、
style='mso-number-format:"\@";'
を指定したデータは、日付に変換されなかったです。（おっ、これか？）
面倒だけど、Excelへ貼り付けるデータ
で
日付が入りそうなデータを出力する場合は、
<td style='mso-number-format:"\@";'>10-01</td>
みたいに、スタイルを指定するのも１つの方法かなぁ。
/*
* ４．サンプルプログラム
*/
せっかくなので、ASPからの出力サンプルを作ってみます。
No.59 結果をExcelで返すならresponse.ContentType=application/vnd.ms-excel
http://www.ken3.org/backno/backno_asp12.html#59
で、
ヘッダ情報にresponse.ContentType=application/vnd.ms-excelと入れると、
Excelファイルとして認識する、そんなことをやったので、
通常表示/Excelファイルの作成とパラメータで切り替えるサンプルを作ってみました
http://www.ken3.org/cgi-bin/test/test079-1.asp
で、普通に画面表示、
http://www.ken3.org/cgi-bin/test/test079-1.asp?MODE=Excel
で、Excelファイルを作成します。
style='mso-number-format:"\@";'の有り無しを交互に作成しているので、
試してみてください。
ポイントは、"を表示させたかったので、""と下記のように書いてます
response.write "<td style='mso-number-format:""\@"";'>9-30</td>"
<%@ language=vbscript %>
<%
If Request.QueryString("MODE") = "Excel" Then 'Excel作成なら
response.buffer = true
'タイプとヘッダがExcelですよ、、と教えてあげるため
response.ContentType = "application/vnd.ms-excel"
response.AddHeader "content-disposition", "inline; filename=dynamic.xls"
Call OUT_DATA() 'データの出力ルーチンを呼ぶ
response.flush
response.end
Else 'パラメータ無しなら
response.write "<html><head><title>"
response.write "style='mso-number-format:"\@";'を指定してExcelへ"
response.write "</title></head><body>"
response.write "↓普通のデータ表示です<hr>"
Call OUT_DATA() 'データの出力ルーチンを呼ぶ
response.write "<hr>↑ここまで。<br>"
response.write "<a href='test079-1.asp?MODE=Excel'>Excel形式の表示</a><br>"
response.write "↑xlsファイルのダウンロード<br>"
response.write "</body></html>"
End If
%>
<% 'データの出力 テーブル形式でデータを出力します
Sub OUT_DATA
response.write "<table Border=1>"
response.write "<tr>"
for i = 1 to 4
response.write "<td width=80>"
response.write i + i
response.write "</td>"
next
response.write "<td width=80><b>=sum(A1:D1)</b></td>"
response.write "</tr>"
'日付と誤解されるデータの出力をチェックする
response.write "<tr>"
response.write "<td>9-30</td>" '9-30普通に表示されるけどExcelへ行くと
' style='mso-number-format:"\@";'を指定しました ↓""は"1つの表現です
response.write "<td style='mso-number-format:""\@"";'>9-30</td>"
response.write "<td>2003-10-01</td>" '2003-10-01違いは感じられないが・・・
response.write "<td style='mso-number-format:""\@"";'>2003-10-01</td>"
response.write "<td>←Excelにすると・・・</td>"
response.write "</tr>"
response.write "</table>"
End Sub |
%>
-【けんぞう！】---------------------------------------------------------
ASPが利用可能なレンタルサーバーをお探しのアナタ、
http://www.ken3.org/asp/server.html ← けんぞうも使っているサーバーの紹介
『おっIISでbasp21でメール送信、mdbも使えるよ』（三流ＰＧ：31歳）
------------------------------------------------------------------------
/*
* ４．終わりの挨拶 ＜／ＨＴＭＬ＞
*/
今回は、9-30が変換されてしまう問題を、
style='mso-number-format:"\@";'
と指定をして、かわしてみました。
違う方法があるかもしれないけど、
何かの参考となれば幸いです。
ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。
ページフッター リンクや広告、質問送信など
三流解説を読んでいただき、どうもです。ここから下は、三流君宛のメッセージ送信や 三流君のホームページの紹介・案内です
目的の情報が見つかったか？少々心配しつつ、、、※質問や感想は、気軽に送ってくださいね。
まぁ、基本はデータの受け取りかなぁ。
・[Form等を使用したデータのやり取り]・・・ASPと言っても、HTMLの入力フォームからデータを受け取ります。POSTやGETでやりとりを押さえますか。
次は、データの入出力 で ADOを使った(ADOで接続) と SQLの解説を少々
・[ADOでMdbファイルを使う]・・・MDBと接続して、簡単な追加・更新・削除を行った。
・[ADOでExcelと接続してみた]・・・.xlsと接続してSQLを使ってみた。
・[ADOでCSVと接続してみた]・・・.CSV テキストを読み出した。※更新・削除はできません
DBが使えるので、あまり使用しないけど、普通のテキストファイル処理
・[テキストファイル処理]・・・ファイルを開いて、書き込む。１行読み込みなどを軽く
VBScriptでFormat関数が無いなど、微妙にVBAと違うけど
[VBScript関数関係の説明]・・・少し、処理を書いてみた。
[その他処理サンプル]・・・あまり良いサンプル作れなかったけど。。。
何かの参考となれば幸いです。
ニガテな環境設定系など
[Win2003 Server に IIS を Setup]・・・ポイントの無い、ほぼ一本道解説だけど。
[IIS 仮想ディレクトの作成とASP動作TEST]・・・Web拡張でASPを有効にしただけです。
Blog:[三流君の作業日記]/
[サンプルコードのゴミ箱]/
広告-[通販人気商品の足跡]
質問や要望など メッセージを送る(三流君に連絡する)
質問や要望など連絡方法でお互い確認が取りやすく、便利なのが掲示板なのですが、私の対応のまずさから不定期で荒れてしまい、掲示板は現在封鎖中です。(反省しなきゃ)
感想や質問・要望・苦情など 三流君へメッセージを送る。
時間的余裕のある要望・質問・苦情の場合は、下記のフォームからメッセージを送ることができます。
急ぎで連絡がほしい、そんな時は:[三流君連絡先アドレス]を見て連絡してください。
[三流君(TOP ken3.org へ戻る)]
/ [ASPで遊ぶ、失敗する]
/ [ASP記事 バックナンバー目次]