示例表如下所示。

+-------+------------+------+
| 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  |
+------+-------+-------------+

我想知道有什么简单的方法来做这个查询吗?

最佳答案

这里有一个方法(虽然有一些漏洞),但无论如何我更喜欢你的。。。

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 |
+----+-------+------------+------+

关于mysql - 如何使用简单的sql查询MySQL中最大记录的所有属性?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/46441630/

10-16 14:41
查看更多