mysql取最大的五组_MySQL 获取分组后每组最大的数据

需求

有两个表：游戏表、视频表，一个游戏对应多个视频，取出排在最前面的 5 个游戏的视频(每个游戏只选取他所有视频的第一个)，视频排序按照视频表的顺序(Video.level)

最终解决方法：

SELECT * FROM `tbl_video` AS `Video` LEFT OUTER JOIN `tbl_game` AS `Game`

ON `Video`.`gameId` = `Game`.`id`

WHERE `Video`.`level` in (SELECT MAX(`level`) FROM `tbl_video` GROUP BY `gameId`) AND `Video`.`isShow` = 1

ORDER BY `Video`.`level` DESC LIMIT 0, 5

网上盛传的子查询解决方案貌似是错误的

数据库 test：

id

name

gameId

level

1

a

1

1

2

b

2

2

3

c

1

3

4

d

1

4

5

e

2

5

运行子查询语句

select * from (select * from `test` order by `level` desc) `temp` group by `gameId` order by `level` desc

得到

id

name

gameId

level

2

b

2

2

1

a

1

1

group by 只是每组取一个代表值，目测没有什么规律(待验证)

解决方法

select * from `test` where `level` in (

SELECT substring_index(group_concat(`level` order by `level` desc SEPARATOR ','),",",1) from `test` GROUP BY `gameId`

) order by `level` desc;

这个比较灵活，可以在 group by 后，再按结果集里的再进行自己想要的字段排序等。

这个可以自由实现获取分组后，第 N 大的数据。比如，分组后，第 N 大的数据：

select * from `test` where `level` in (

SELECT substring_index(substring_index(group_concat(`level` order by `level` desc SEPARATOR ','),",",N), ",",-1)

from `test` GROUP BY `gameId`

) order by `level` desc;

将 N 改为你自己想要的数字即可(但是超过数量以后会求最末尾的那一个)，而最上面的方法只能求最大最小。