问题

一场游戏活动，很多用户一起参与，每个用户都能参与多次，现在要获取得分排行榜，当点数相同的情况下，先出点数的用户赢,同时同一用户相同点数的多条记录,取最先的那条.

解决

方案一

SELECT * FROM (SELECT * FROM (SELECT id,game_activity_id,user_uuid,nickname,avatar,score,shop_table_id,shop_table_title,status,ROW_NUMBER() OVER ( PARTITION BY user_uuid ORDER BY score DESC, id ASC ) AS row_num FROM mzl_game_record WHERE game_activity_id = 667 AS ranked_records WHERE row_num = 1 ) AS t1 ORDER BY t1.score DESC,t1.id ASC

方案二

SELECT t1.*
FROM mzl_game_record t1
INNER JOIN (
SELECT DISTINCT(id) id
FROM mzl_game_record where game_activity_id=667
ORDER BY score DESC,id asc) AS t2 ON t2.id = t1.id
GROUP BY t1.user_uuid ORDER BY t1.score desc,t1.id asc