SQL 集計関数MAXと副問い合わせ(サブクエリー)

本文(発行内容)

＜SQL 集計関数MAXと副問い合わせ(サブクエリー)＞
こんにちは、三流プログラマーのＫｅｎ３です。

今回は、SQLの集計関数MAXと副問い合わせ(サブクエリー)を使ってみます。

/*
 * １．今回のキッカケ
*/

掲示板
http://www.ken3.org/cgi-bin/bbs/asp/wforum.cgi
で下記の質問をもらいました。

-----
タイトル：あまり初歩的ですいません
ACCESS97のテーブル（下記）に「konyu」というテーブルがあり、
フィールド ID 数値型　とno　数値型があったとします。
IDの最大値のレコードのみ取得する場合
SQL ="SELECT * from kounyu where ID =(SELECT MAX(ID) from 
kounyu)"で取得出来るのですが、何故かnoの最大値のレコードを取
得できません。
SQL1 ="SELECT * from kounyu where no =(SELECT MAX(no) from kounyu)"ではダメ
なのでしょうか？
IDは１〜順番に付与され全レコードデータが入ってます
noは後からinsert文で番号を入力するため、データが入ってたり
入ってなかったりします。
noのSQLを実行すると下記にエラーが表示されます。
「要求された名前、または序数に対応する項目がコレクションで見
つかりません。」
-----

ヤバイ・・・初歩的な問合せがわからないや・・・
最近、初歩的と言われる問合せでハマッテマス・・・見かけ倒しの実力なので。

/*
 * ２．集計関数MAX
*/

SQLには、集計関数があって、SUM(項目名)だと、合計を計算してくれました。
あと、有名どころでは、MAX(項目名)で、最大値を返してくれます。

下記のテストデータを作成しました。
テーブル名:kounyu
ID no  名前       金額  数量
1  100 Ken3       2000     5
2   20 けんぞう    100   200
3  250 あいうえお 1500   100
5    5 かきくけこ   55    55
6   10 さしすせそ   60   150

Select MAX(ID), MAX(no), MAX(金額), MAX(数量)
  From konyu
と、SQL文を作成してみた。 

  'MAX関数を使用したSQL文を作成する
  strSQL = "Select MAX(ID), MAX(no), MAX(金額), MAX(数量) "
  strSQL = strSQL & " From kounyu "
こんな感じで、文字列を作成して、
  Set rs = db.Execute(strSQL) 'SQL発行、レコードセットの作成
で、レコードセットを作成してみました。

すると、結果は、
Expr1000 Expr1001 Expr1002 Expr1003 
6        0        2000     200 
んっ？
項目名が勝手に付いているのはまぁゆるすとして、
MAX(no)が０だよ・・・なぜ？250が返ってくれないとね。


↑なぜかMAX(no)で０が返ってきた結果画面

なんか、noって名前が怪しいんだろうなぁと勝手な予想。
よく聞く話だと、予約語って感じかなぁ。
[]で囲うと動く、そんな話も目にしてたので、SQL文を少し細工する。

  'MAX関数を使用したSQL文を作成する
  strSQL = "Select MAX(ID), MAX([no]), MAX(金額), MAX(数量) "
  strSQL = strSQL & " From kounyu "
と修正してみると、

http://www.ken3.org/cgi-bin/test/test089-1.asp
で、テストすると、なんとかOKですね。
no等の予約語は、[]で囲うと正しくフィールドとして認識される。
^^^^^^^^^^^^^^^^^^^^^^^^^^
頭のスミに入れて置いてください。

--- 枠外解説・愚痴 ---
フィールド名の頭に必ずF_と接頭語じゃないけど、付けて設計する先輩が居た。
そんな人達は、今回の[no]問題／予約語には、引っかからないのだろう。
コーディングミスを誘発しないテーブル設計ってのも必要ですね。

/*
 * ３．副問い合わせ(サブクエリー)を使い、最大値の行を表示してみる
*/

掲示板の質問で勉強した・・・なんて言っていると怒られるけど、

テーブル名:kounyu
ID no  名前       金額  数量
1  100 Ken3       2000     5
2   20 けんぞう    100   200
3  250 あいうえお 1500   100
5    5 かきくけこ   55    55
6   10 さしすせそ   60   150

とデータがあった時に、
・一番多く購入した人のデータ
・noが一番大きい人
を検索する場合

プログラムの組み方は、いろいろとあると思います。
データが少ないなら、全てのデータを頭から見ていき、
最大値のデータを代入する、そんな方法もありますが、
SELECT文の出力結果をもう一つのSELECT文のWhereの条件にする、
サブクエリーやクエリーの埋め込み、そんな方法をテストしてみます。

なんか変な日本語の説明でしたが、
Select 列名
 From  テーブル名
　Where 列名 = (ここに条件として使いたいSQLをもう一つ書き込むことができます)

・一番多く購入した人のデータ
を取り出すSQL文を下記のように作成することが出来ます。

  '数量が一番大きい人を探すため、
  'MAX関数を埋め込んだサブクエリー付のSQL文を作成する
  strSQL = "Select 名前 "
  strSQL = strSQL & " From kounyu "
  strSQL = strSQL & " Where 数量 = (Select MAX([数量]) From kounyu)"

なんだぁ？こんなもん？って感じですが、
SQL文の幅が広がったと思います。

http://www.ken3.org/cgi-bin/test/test089-2.asp
で、テストできます。

サブクエリー／SQL副問合せ、頭のスミにでも入れて置いてください。

/*
 * ４．終わりの挨拶　＜／ＨＴＭＬ＞
*/

今回は、
MAX関数を使った時、noのフィールド名は予約語なので[no]としてください。
サブクエリー／ＳＱＬ副問合せを使ってみました。

いろいろと問題点を残しつつ、脱線・寄り道の多いメルマガですが、
今後とも、よろしくお願いします。

何かの参考となれば幸いです。

ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。

フィードバック

評価・感想

三流君の主なリンク先

ＡＳＰで遊ぶ、失敗する	掲示板	バックナンバー	登録と解除
三流プログラマー 業務の愚痴	掲示板	バックナンバー	登録と解除
ＶＢＡで楽しくプログラミング	掲示板	バックナンバー	登録と解除
コンビニのオモテとウラ話	掲示板	バックナンバー	登録と解除
www.ken3.org(サイト内)から を利用して、 を

その他 宣伝広告