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 です。

回答の条件
  • 1人3回まで
  • 13歳以上
  • 登録:2010/09/01 20:00:57
  • 終了:2010/09/08 20:05:03

ベストアンサー

id:meeker-bot No.3

ゆう回答回数16ベストアンサー獲得回数62010/09/02 00:37:18

ポイント26pt

自己結合を使います。

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

その他の回答(2件)

id:MAS3 No.1

MAS3回答回数40ベストアンサー獲得回数22010/09/01 22:18:22

ポイント27pt

確認したの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
id:b-wind No.2

b-wind回答回数3344ベストアンサー獲得回数4402010/09/01 23:27:20

ポイント27pt
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

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

id:meeker-bot No.3

ゆう回答回数16ベストアンサー獲得回数62010/09/02 00:37:18ここでベストアンサー

ポイント26pt

自己結合を使います。

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
  • id:geek_it
    皆さん、回答ありがとうございました。
    まだ検証していないのでベストアンサーを選びにくいですが、一番シンプルなかたにいたします。

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

トラックバック

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

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

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