mysql求游戏排名

Question

1.数据结构

CREATE TABLE `active_gamescore` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `active_id` int(11) NOT NULL DEFAULT '0' COMMENT '关联active.id',
  `user_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '参与用户',
  `score` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '分数',
  `created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间（时间戳）',
  PRIMARY KEY (`id`),
  KEY `active_id` (`active_id`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=240076 DEFAULT CHARSET=utf8 COMMENT='小游戏分数记录';

2.数据样例

id	active_id	user_id	score	created_at
240075	58	283	300	1483203758
193979	58	283	300	1483203759
66457	58	31	300	1483205803
227883	58	258	300	1483210053
24923	58	295	300	1483210380
97130	58	124	300	1483210871
83280	58	154	300	1483215285
96737	58	75	300	1483217001
114577	58	69	300	1483220107
68010	58	267	300	1483220189
23048	58	281	300	1483223949
168580	58	189	300	1483230406
143775	58	9	300	1483231469
84925	58	21	300	1483232858
161099	58	7	300	1483234003
52420	58	36	300	1483235310
145730	58	75	300	1483237517
67370	58	119	300	1483240950
161060	58	278	300	1483244220
92743	58	123	300	1483251658

3.求：分数最高的头20条记录的sql。
要求：
1.一个user_id只有一个名额，取最高分的那条；
2.同分的取时间靠前的那条

以上

Answer 1

select user_id,a.sc,min(created_at) tm 
from (select user_id,max(score) sc from active_gamescore group by user_id) a 
join active_gamescore b 
on a.user_id=b.user_id and a.sc=b.score 
group by a.user_id,a.sc 
order by a.sc desc,tm asc limit 20;

Answer 2

select t.userid,t.score from (select * from active_gamescore order by score desc,created desc) as t group by t.userid limit 20;

Answer 3

更新：
一个人只能回复一次，很忧伤
感谢几位抽出时间去帮我解题
测试了一下(5次平均)

在1w数据量的情况下，
@clcx_1315 ：0.004s
@伊拉克 : 0.009s
@邢爱明 :0.006s
我自己的 ：0.016s

在20w的数据量下：
@clcx_1315 ：0.104s
@伊拉克 : 0.141s
@邢爱明 :0.165s
我自己的 ：0.171s

所以@clcx_1315的方法最优，十分感谢，学到了一个思路。
从explain看，@clcx_1315的写法只做了2次全表遍历，其他都是3次，或许这就是原因了。

===========================之前的分隔线============================
琢磨了一种写法，但效率有待提高

select ta.user_id,ta.max_score,tb.min_time
from (
        select a.user_id, max(a.score) max_score from  active_gamescore a where  a.active_id='58' group by a.user_id
        ) ta
join (
        select a.user_id,a.score,min(a.created_at) min_time from active_gamescore a where  a.active_id='58' group by a.user_id,a.score
        ) tb 
        on ta.user_id=tb.user_id and ta.max_score=tb.score

order by ta.max_score desc,tb.min_time asc 
limit 20

Answer 4

假设同一用户下的created_at字段值不重复，可以试试下面的语句：

SELECT t1.user_id, t1.score, t1.created_at
FROM (
        SELECT
            @row_num:=IF(@prev_col1=t.user_id, @row_num+1, 1) AS row_number,
            t.*,
            @prev_col1:=t.user_id
        FROM (SELECT * FROM active_gamescore ORDER BY user_id, score DESC, created_at) t,
             (SELECT @row_num:=1, @prev_col1:=NULL) var
) t1 WHERE row_number = 1
ORDER BY t1.score DESC, t1.created_at
LIMIT 20

Answer 5

SELECT
    yws0.user_id,
    yws0.score,
    min(create_time) AS create_time
FROM
    active_gamescore yws0
WHERE
    (user_id, score) IN (
        SELECT
            yws.user_id,
            yws.max_score
        FROM
            (
                SELECT
                    user_id,
                    max(score) AS max_score
                FROM
                    active_gamescore
                GROUP BY
                    user_id
            ) yws
    )
GROUP BY
    yws0.user_id,
    yws0.score
ORDER BY
    SCORE DESC
LIMIT 3