mysql语句查询最高分且耗时最短的例子

数据

INSERT INTO `dsdd`.`test` (`id`, `score`, `duration`, `user_id`) VALUES (1, 80, 8, '2');
INSERT INTO `dsdd`.`test` (`id`, `score`, `duration`, `user_id`) VALUES (2, 80, 5, '2');
INSERT INTO `dsdd`.`test` (`id`, `score`, `duration`, `user_id`) VALUES (3, 60, 3, '2');
INSERT INTO `dsdd`.`test` (`id`, `score`, `duration`, `user_id`) VALUES (4, 80, 8, '3');
INSERT INTO `dsdd`.`test` (`id`, `score`, `duration`, `user_id`) VALUES (5, 80, 5, '3');
INSERT INTO `dsdd`.`test` (`id`, `score`, `duration`, `user_id`) VALUES (6, 60, 3, '3');

查询（不成功）

SELECT
	user_id,
	MAX( score ),
	duration 
FROM
	( SELECT user_id, score, duration FROM test ORDER BY score DESC, duration ) a 
GROUP BY a.user_id

查询1

SELECT
						 user_id
						,answer_time
						,correct_topic_number
						,answer_duration
						,department_id
						,department_name
						,create_by
						,ROW_NUMBER() over (PARTITION BY user_id,answer_time ORDER BY correct_topic_number desc,answer_duration asc ) AS rank_num
			 FROM sys_study_activity_answer