人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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

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

●質問者: Ethinos
●カテゴリ:コンピュータ
✍キーワード:いもの クエリ テスト データベース レコード
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● Mook
●27ポイント

やはりこれを一度の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/


2 ● mamy1326
●27ポイント

SELECT

テスト日,

教科,

生徒番号,

点数

FROM

テスト結果

ORDER BY

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

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

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

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

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

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

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

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

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

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

LIMIT 0,1

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

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


3 ● mikuni1966
●26ポイント

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

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

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ