データベース操作、SQLについて教えてください。

 これまでのテスト結果を管理しているテーブルがあります。内容は、(テスト日、教科、生徒番号、点数)となっています。このテーブルから、クエリを用いて、各テスト日の教科ごとに最も点数の高い生徒番号のレコードを抽出したいと思っています。ただし、最高得点が同点の場合は、生徒番号の最も若いものとします。

 現在は一度に抽出する方法が分からないため、①一度各テスト日の教科別に最も高い点数を抽出し、②この点数の学生の中で最も若い生徒番号を抽出、③元のレコードから当該レコードを抽出という3つのクエリで行っています。できればこれをワンステップで行いたいのですがどのようにすればよいでしょうか?

回答の条件
  • 1人3回まで
  • 登録:2010/01/16 13:39:22
  • 終了:2010/01/23 13:40:02

回答(3件)

id:Mook No.1

Mook回答回数1312ベストアンサー獲得回数3912010/01/16 16:11:13

ポイント27pt

やはりこれを一度のSQL で実行するのは大変そうですので、VBA を使用したやり方です。

(単純なSQLでの解でなくてすみません。)


やり方は

(1)Access起動後 Ctl+F11

(2)表示されたウィンドウで左のデータベース名で右クリックし挿入⇒標準モジュール

(3)そこで表示されたウィンドウに下記をコピーします(TBL_NAME は実際のテーブル名に変更)。

(4)F5を押して ListTopScore を選択して実行

で「実行結果」というテーブルが作成されます。


再度実行する場合は、「実行結果」のテーブルをAccess上で閉じてから実行ください。

Option Compare Database

Sub ListTopScore()
' ★★★下記は実際のテーブル名に変更
    Const TBL_NAME = "[テスト結果]"
    
    Dim DB As DAO.Database
    Set DB = CurrentDb
    
    Dim dRS As DAO.Recordset
    Dim sRS As DAO.Recordset
    Dim tRS As DAO.Recordset
    Dim rRS As DAO.Recordset

' 表示用テーブル
    On Error Resume Next
        Dim tbl As Object
        Set tbl = CurrentDb.TableDefs("実行結果")
    On Error GoTo 0
    
    DoCmd.SetWarnings False
    If tbl Is Nothing Then
        DoCmd.RunSQL "SELECT " & TBL_NAME & ".* INTO 実行結果 FROM " & TBL_NAME & " WHERE 1 = 0;"
    Else
        DoCmd.RunSQL "DELETE FROM 実行結果;"
    End If
    DoCmd.SetWarnings True
    
    Set rRS = DB.OpenRecordset("SELECT * FROM 実行結果")

    Dim sql As String
' テスト日を取得
    sql = "SELECT DISTINCT テスト日 FROM " & TBL_NAME
    Set dRS = DB.OpenRecordset(sql)
    Do Until dRS.EOF
' 各テスト日の教科を取得
        sql = "SELECT DISTINCT 教科 FROM  " & TBL_NAME & " WHERE テスト日=#" & dRS!テスト日 & "#"
        Set sRS = DB.OpenRecordset(sql)
        Do Until sRS.EOF
' 各テスト日・教科を点数順(降順)・生徒番号に取得
            sql = "SELECT * FROM " & TBL_NAME & " WHERE 教科=""" & sRS!教科 & """" _
                & " AND テスト日=#" & dRS!テスト日 & "#" _
                & " ORDER BY 点数 DESC,生徒番号"
            Set tRS = DB.OpenRecordset(sql)
            rRS.AddNew
            rRS!テスト日 = tRS!テスト日
            rRS!教科 = tRS!教科
            rRS!点数 = tRS!点数
            rRS!生徒番号 = tRS!生徒番号
            rRS.Update
            sRS.MoveNext
        Loop
        dRS.MoveNext
    Loop
End Sub

ファイルを保存しておけば、次回以降はマクロの実行だけで結果が取得できます。

http://www.geocities.jp/cbc_vbnet/

id:mamy1326 No.2

mamy1326回答回数16ベストアンサー獲得回数12010/01/16 17:04:08

ポイント27pt

SELECT

  テスト日,

  教科,

  生徒番号,

  点数

FROM

  テスト結果

ORDER BY

  テスト日 DESC, -- まずはテスト日の一番新しい順に並べ、

  教科 ASC,   -- 同じ日の教科別に並べ、

  点数 DESC,   -- 同じ日の同じ教科の点数の高い順に並べ

  生徒番号 ASC  -- 同じ点数があった場合に生徒番号の若い順に並べる

というクエリで可能かと思います。

検証はしていませんが・・・。

僕はOracleとMySQLとPostgreSQLしか知りませんが、ORDER BY は上から順に解釈されるので、

この方法で絞り込みながら並び替えができるかと。

僕は今までこの方法でやっています。

オンリーワンを取りたい場合は最後に

LIMIT 0,1

と付ければ一番上位の1行だけ取りますし、日付や他の条件などで絞る前提ならば、WHERE句で絞ればよいかと思います。

もし教科がテキストで「数学」というような形式で保存されている場合は、できれば教科マスタなどを作ってフラグ値で持たせたほうが操作しやすいかもしれないですね。

id:mikuni1966 No.3

mikuni1966回答回数106ベストアンサー獲得回数12010/01/16 19:54:44

ポイント26pt

テスト日、教科 で集計キー にして 点数 をMAX とする。 ORDER BY は テスト日、教科、生徒番号 の昇順。

SQL文については、DBによって違うので・・。

  • id:Mook
    使っているDBは何ですか?
    またクエリの実行はどのように行っているのですか?
    (コマンドライン?Web I/F?)

    環境によって、いろいろとやり方が変わってくると思います。
  • id:Ethinos
    使っているDBはアクセスです。
    あんまり詳しいことはすいませんが分かりません
  • id:Mook
    回答中 Ctl+F11 と書いたのは Alt+F11 ですので、読み変えてください。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません