mysql - 如何用简单的sql查询MySQL中最大记录的所有属性？

Question

示例表如下所示。

+-------+------------+------+
| score | created    | uid  |
+-------+------------+------+
|    89 | 2017-08-01 |  101 |
|    69 | 2017-08-02 |  101 |
|    99 | 2017-08-03 |  101 |
|    79 | 2017-08-03 |  102 |
|    69 | 2017-08-04 |  102 |
|    90 | 2017-08-02 |  102 |
+-------+------------+------+

我想查询所有用户的最高分，所以我尝试下面的sql。

select uid, max(score) as max_score from minTable group by uid;

我得到了一个结果。

+------+-----------+
| uid  | max_score |
+------+-----------+
|  101 |        99 |
|  102 |        90 |
+------+-----------+

现在，我希望知道最大行数的创建日期!我知道我可以使用子查询来获取它。像下面的sql

select a.uid, a.score, a.created
from minTable a
INNER JOIN 
(select uid, max(score) as max_score from minTable group by uid)b
ON a.uid=b.uid and a.score=b.max_score;

然后似乎得到了预期的结果。

+------+-------+------------+
| uid  | score | created    |
+------+-------+------------+
|  101 |    99 | 2017-08-03 |
|  102 |    90 | 2017-08-02 |
+------+-------+------------+

在使用以下 sql 插入更多记录后。

insert into minTable (uid, score, created) values (102, 90, "2017-08-01");
insert into minTable (uid, score, created) values (102, 90, "2017-08-05");

我再次尝试使用子查询进行查询。

+------+-------+------------+
| uid  | score | created    |
+------+-------+------------+
|  101 |    99 | 2017-08-03 |
|  102 |    90 | 2017-08-02 |
|  102 |    90 | 2017-08-01 |
|  102 |    90 | 2017-08-05 |
+------+-------+------------+

每个uid不止一条记录，我知道我可以再次分组!像下面的方式。

SELECT c.uid, c.score, min(c.created) as min_created
FROM (
select a.uid, a.score, a.created
from minTable a
INNER JOIN 
(select uid, max(score) as max_score from minTable group by uid)b
ON a.uid=b.uid and a.score=b.max_score)c GROUP BY c.uid, c.score;

得到了预期的结果:

+------+-------+-------------+
| uid  | score | min_created |
+------+-------+-------------+
|  101 |    99 | 2017-08-03  |
|  102 |    90 | 2017-08-01  |
+------+-------+-------------+

我想知道是否有任何简单的方法来执行此查询？

Answer 1

这是一种方法(尽管存在某些漏洞)，但无论如何我更喜欢你的...

SELECT *
  FROM mintable
 WHERE FIND_IN_SET(id,(SELECT GROUP_CONCAT(id ORDER BY score DESC,created) FROM mintable)) <= 2;
+----+-------+------------+------+
| id | score | created    | uid  |
+----+-------+------------+------+
|  3 |    99 | 2017-08-03 |  101 |
|  7 |    90 | 2017-08-01 |  102 |
+----+-------+------------+------+