示例表如下所示。
+-------+------------+------+
| 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/