Mysqlで、項目の最大値に同じ値があった場合の処理について

Question

例として、こういったテーブルがあったとします。
それぞれの生徒のテストの結果を挿入したテーブルです。

id(auto_increment),user_id,name,sub,point

1,1,田中,国語,80
2,1,田中,数学,90
3,1,田中,英語,90
4,1,田中,理科,70
5,2,佐藤,社会,100
6,2,佐藤,数学,70
7,3,山本,国語,80
8,3,山本,英語,50
9,3,山本,数学,60

こういったテーブルで、各生徒毎にもっとも点数がよかった教科を抽出したいです。かつ、もっとも点数が良かった教科が複数あった場合（同じ点数の場合）、カンマ区切りで出力したいです。（できなければ別レコードで複数のせたいです。）

【出てほしい結果】
user_id,name,sub
1,田中,'数学,英語'
2,佐藤,'社会'
3,山本,'国語'

もしくは、

user_id,name,sub
1,田中,'数学'
1,田中,'英語'
2,佐藤,'社会'
3,山本,'国語'

このように出力したいです。
自分ではいくら考えても上記の結果が出力できなかったので、こういったSQL文を発行する方法がありましたら是非ご教示ください。
よろしくお願いします。

Answer 1

相関サブクエリで max を条件にする。

select t.user_id, t.name, t.sub
from t
where
  t.point = (
    select max(point) from t t2 where t2.user_id = t.user_id group by t.user_id
  )

自己結合で group by ... having で絞る。

select
  t.user_id, t.name, t.sub
from
  t inner join t as t2 on t.user_id = t2.user_id
group by
  t.id, t.user_id, t.name, t.sub, t.point
having 
  t.point = max(t2.point)

定番のパターンです。

---

カンマ区切りにするなら group_concat を使う（MySQL 限定）。

select
  user_id, name, group_concat(sub) as sub
from (
  select
    t.user_id, t.name, t.sub
  from
    t inner join t as t2 on t.user_id = t2.user_id
  group by
    t.id, t.user_id, t.name, t.sub, t.point
  having 
    t.point = max(t2.point)
) as x
group by
  user_id, name

Comments

[sanset]

非常に連絡遅れて申し訳ありません。無事再現できました。
順にやり方まで説明していただいてありがとうございます。

Answer 2

相関サブクエリを使って最高点を割り出し、それを条件にDBを引きます。

SELECT user_id, `name`,sub FROM scores
WHERE point = (SELECT MAX(point) FROM scores AS scores2 WHERE scores.user_id=scores.user_id)

Comments

[sanset]

非常に連絡遅れて申し訳ありません。ありがとうございます。
お二人にベストアンサーしたいのですが、し様で一人しかできないため、
さらに詳しい例を頂いた方をベストアンサーにしました。申し訳ありません。
またよろしくお願いします。