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

mysqlで重複した列で特定条件の二列をとりだしたいのですが、どのようなSQL文になりますでしょうか。

以下のようなテーブルがあります。

ID|名前|点
1 |山田|20
2 |坂本|40
3 |山田|60
4 |杉本|40
5 |内田|30
6 |内田|70
7 |山田|80
8 |坂本|70
9 |山田|60


名前の部分で重複した場合、点が多いもの二つを取り出したいのです。

結果は以下のようになれば良いです。

ID|名前|点
7 |山田|80
3 |山田|60
8 |坂本|70
2 |坂本|40
6 |内田|70
5 |内田|30
4 |杉本|40


環境は mysql ver5.0.77 + centos5 です。

●質問者: デジタルケイタ
●カテゴリ:コンピュータ ウェブ制作
✍キーワード:MySQL SQL いもの 内田 名前
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● MAS3
●27ポイント

確認したのmysqlのバージョンが5.1ですが、次のSQLでとれました。

テーブル名はtestとしています。

分析関数なしだと面倒ですね。

select
 ID, 名前, 点
from
(
 select
 (
 select
 sum(
 case
 when t.点 < 点 then 1
 when t.点 = 点 and t.ID > ID then 1
 else 0
 end
 ) + 1
 from test
 where t.名前 = 名前
 ) as rank,
 ID,
 名前,
 点
 from
 test as t
) as t
where
 rank <= 2
order by
 名前,
 点 desc

2 ● b-wind
●27ポイント
SELECT point_list.* FROM 
 ( SELECT 名前,MAX(点) AS 最高点 FROM テーブル GROUP BY 名前 HAVING count(*) >=2 ) as name_list
 JOIN
 テーブル as point_list
 ON ( name_list.名前 = point_list.名前 ) 
 WHERE point_list.点 = 最高点 OR point_list.点 =
 ( SELECT MAX(in_list.点) FROM テーブル AS in_list WHERE in_list.名前 = point_list.名前 AND in_list.点 <> name_list.最高点 )
ORDER BY point_list.名前, point_list.点 DESC

これでもでるかな?確認はしてない。


3 ● ゆう
●26ポイント ベストアンサー

自己結合を使います。

SELECT *
FROM test i
WHERE (
SELECT COUNT(1) 
FROM test j
WHERE j.名前 = i.名前
 AND (j.点 > i.点 OR (j.点 = i.点 AND j.ID > i.ID))
) < 2
ORDER BY 名前, 点 desc
関連質問


●質問をもっと探す●



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