22
14

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

Mysql で各グループの上位〇件までを取得する。(変数もlimitも使わないよ)

Posted at

###クラス別の上位3位までのデータを取得したい。(同率の場合は名前昇順)

####・得点テーブル

クラス 名前 得点
1組 A 53
1組 B 82
1組 C 75
1組 D 26
1組 E 19
2組 F 100
2組 G 80
2組 H 80
2組 I 88
3組 J 50
3組 K 85
3組 L 41

####上記をこうしたい

クラス 名前 得点
1組 B 82
1組 C 75
1組 A 53
2組 F 100
2組 I 88
2組 G 80
3組 K 85
3組 J 50
3組 L 41

####・取得SQL

SELECT 
a.クラス, a.名前, a.得点
, find_in_set(a.名前, b.rank) as 順位
FROM 得点テーブル a 
INNER JOIN (
   SELECT クラス, SUBSTRING_INDEX(GROUP_CONCAT(名前 ORDER BY 得点 DESC, 名前), ',', 3) as rank
   FROM 得点テーブル
   GROUP BY 1 
) b on a.クラス = b.クラス
WHERE FIND_IN_SET(a.名前, rank)
ORDER BY a.クラス, 順位

####・解説
GROUP_CONCATでグループ毎オーダー順に並べつつカンマ区切りの文字列として取得し、
SUBSTRING_INDEXでカンマ区切りの必要数でカットすることで上位の順位別キーのCSV文字列が取得できる。
FIND_IN_SETでキーとマッチするものだけ取得する。
FIND_IN_SETは文字列の何番目(先頭を1として)にマッチしたかを返してくれるのでそのまま順位になる。

####・別解 havingを使う

SELECT 
a.クラス, a.名前, a.得点
, find_in_set(a.名前, b.rank) as 順位
FROM 得点テーブル a 
INNER JOIN (
   SELECT クラス, GROUP_CONCAT(名前 ORDER BY 得点 DESC, 名前) as rank
   FROM 得点テーブル
   GROUP BY 1 
) b on a.クラス = b.クラス
HAVING 順位 <> 0 and 順位 < 4
ORDER BY a.クラス, 順位

####※注意
GROUP_CONCAT で連結できる文字数はDB設定に依存するので長くなりすぎる場合は設定の変更が必要。

22
14
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
22
14

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?