需求
有两个表:游戏表、视频表,一个游戏对应多个视频,取出排在最前面的 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 改为你自己想要的数字即可(但是超过数量以后会求最末尾的那一个),而最上面的方法只能求最大最小。