グループ化したSQLの書き方について

質問

id,stpass,tokuten,branch,dateというフィールドをもつresultというテーブルを作成しました。
最高得点(フィールド名：tokuten）は、下記SQL（クエリーで作りました）でできます。
SELECT result.id, result.stpass, Max(result.tokuten) AS tokutenの最大
FROM result GROUP BY result.id, result.stpass;
ただ、最高得点（Max(result.tokuten) AS tokutenの最大）を出したと同じレコードのそれ以外のフィールド（branch,date）
を表示させるSQL（クエリー）がわかりません。

何方か、ご存知の方おられましたら、ご教授ください。

回答No.3

jamshid6 · ベストアンサー

２つ以上の項目を出すならば、２つ目の構文（JOIN構文）を使わないと厳しいです。
少し長くなりますが、自己最高得点を２回以上マークすることもあると思うので、その場合は最新の日付を出しています。

select s.branchname,r.date,s.stuname,r.maxpercent
from m_student s
left outer join
(select r1.stuid,max(r1.date) date,r1.percent maxpercent from result r1
where r1.percent in
(select max(r2.percent) from result r2
where r2.stuid=r1.stuid)
group by r1.stuid,r1.percent) r on r.stuid=s.stuid

標準構文とは言え、MySQLは手元にないので、別のDBMSでしか稼働確認していません。もし通らないようなら、MySQLカテゴリで識者を探す方が効率がいいかもしれません。
（経験上の得手不得手というのはありますので）

お礼

これで動きました。
何から何までご指導いただき
大変ありがとうございました。
深く感謝いたします。

回答No.2

jamshid6

>SQL文はどの言語でも同じだと聞いています。
シンプルなSQL文についてはそのとおりです。しかし、実際のDBMSがすべてSQL標準に則った構文をサポートしているかというと、そうではありません。また、バージョンによってもサポート状況は変わります。
（MySQLとかは特にそうです）

ご質問の件については、２つ例をあげます。
1) select s.stuname,
(select max(tokuten) from result where id=s.stuid)　maxtokuten
from m_student s
2) select s.stuname,r.maxtokuten
from m_student s
left outer join
(select id, max(tokuten) maxtokuten
group by id) r on r.id=s.stuid

stpassのフィールドの意味がわからないので、とりあえずm_studentのstuidとresultのidをJOINさせる前提で書いていますが、書き方のポイントはおわかりいただけるのではと思います。

お礼

ありがとうございました。
$sql="select s.branchname,s.stuname,(select max(percent) from result where stuid=s.stuid)　maxpercent from m_student s";
で通りました。
教えていただいたtokutenは、実際はpercentです。わかりやすくする為に書きました。

補足

$sql="select s.branchname,s.stuname,(select max(percent) from result where stuid=s.stuid)　maxpercent from m_student s";
では通るのですが、
$sql="select s.branchname,s.date,s.stuname,(select max(percent) from result where stuid=s.stuid)　maxpercent from m_student s";
では、通りません。
エラーメッセージは
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in ＊＊＊
です。
テーブルのフィールドは
result：id,cate_a_code,cate_b_code,course,stuid,stpass,date,percent,judgment,branch,branchname,stuname,stuyomi
m_student:id,bankcode,stuid,stpass,stuname,stuyomi,d_from,d_to,branchname
です。

resultテーブルの該当レコード（最高得点）のdateを表示させるSQLがわかりません。

初心者ですいません。

回答No.1

jamshid6

何のDBをお使いですか？
以下が動けば、目的は達すると思いますが。。
select * from result r
where tokuten in
(select max(tokuten) from result
where id=r.id and stpass=r.stpass)

お礼

ACCESS上で確かに動きました。ありがとうございました。

補足

DBはMySQL5です。（言語PHP5）
お教えいただいたSQLで動きました。

SQL文はどの言語でも同じだと聞いています。

以下はご存知の言語を基にしたお答えで結構です。
基本を教えていただいた所で、以下の事をやろうとしたのですが、
できません。

resultテーブルは、試験結果で、試験を受けた人の結果だけが入っています。
試験を受けていない人もいます。
全員(テーブル:m_student)の一覧と試験を受けた人を出したいのですが。
A氏：未受験
B氏：95点（何回か受けたうちの最高得点）
C氏：60点（何回か受けたうちの最高得点）
D氏：未受験
のように表示したいのですが。

テーブル：m_studentには、id,stuid（ID）,stuname（氏名）のフィールドがあります。

１．left joinでresultテーブルとm_studentテーブルを結合
２．tokutenフィールドがある人は、その人の最高点を表示
３．tokutenフィールドがない人（m_studentにしか存在しない）は名前だけ表示

というSQLを書きたいのですが、思いつきません。
私は、ACCESS＆VBAはある程度かじっていますが、ダイレクトにSQLを書くのは、
業務上では初めてです。（勉強は少ししたつもりですが）

説明が足りないかもしれません。
また、突拍子もない質問かもしれません。
恐れいります。よろしくお願いします。