MySQL将记录数限制为一列中的5个唯一值 [英] MySQL Limit number of records to 5 unique values in one column
问题描述
我正在尝试根据游戏中的杀戮次数创建排行榜,该游戏显示排名前5位的玩家(杀戮次数相同的人处于同一等级).
i am trying to create a leaderboard based on numbers of kills in a game where it shows top 5 ranking players (people with the same number of kills are in the same rank).
我如何获取直到第5位的所有记录(直到一列(杀死)中最多有5个唯一值的所有记录).
How do i get all records up until rank 5 position (all records until a max of 5 unique values in a column(kills)).
我希望这是有道理的.
查询还需要考虑地图.例如:WHERE map ='map_name'
The query will also need to take into account the map. For Example: WHERE map = 'map_name'
例如:
表格:
Name, Map, Kills
user0 - map2 - 30
user1 - map1 - 30
user2 - map1 - 27
user3 - map1 - 54
user4 - map1 - 34
user5 - map1 - 34
user6 - map1 - 27
user7 - map1 - 22
user8 - map1 - 22
user9 - map1 - 31
user10 - map1 - 21
user11 - map1 - 27
user12 - map2 - 34
需要结果(地图名称为map1的前5名中的所有玩家/记录):
Result Needed (all players / records within the top 5 ranks with the map name map1):
1. user3 - 54
2. user4 - 34
user5 - 34
3. user9 - 31
4. user1 - 30
5. user2 - 27
user6 - 27
user11 - 27
到目前为止,我有: SELECT * FROM
records WHERE
map ='map1'ORDER BY
kills DESC
So far i have: SELECT * FROM
recordsWHERE
map= 'map1' ORDER BY
killsDESC
但是我需要将返回的行数限制为前5个排名
所需的数量,而不是所有行.
but i need to limit the number of rows returned to only the amount needed for the top 5 ranks
, rather then all the rows.
推荐答案
尝试以下查询:
SELECT * FROM records WHERE
map = 'map1' and kills in (SELECT * FROM
(select Kills from records where map = 'map1' group by Kills order by kills desc limit 0,5)
as x) order by Kills desc
这篇关于MySQL将记录数限制为一列中的5个唯一值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!