mysql - 如何在mysql中找到每天最好的球员

Question

我有一个像这样的 mysql 表:

+----+--------+-------------+-------+--------------+---------------------+
| id | name   | country     | score | email        | timestamp           |
+----+--------+-------------+-------+--------------+---------------------+
|  2 | John   | Canada      |   360 | john@abc.com | 2015-01-28 14:23:35 |
|  3 | Peter  | USA         |   830 | NULL         | 2015-01-28 16:23:35 |
|  4 | Victor | New Zealand |   780 | NULL         | 2015-01-27 06:33:55 |
|  5 | Sandy  | UK          |   880 | NULL         | 2015-01-29 02:09:58 |
|  6 | Craig  | New Zealand |   980 | NULL         | 2015-01-29 02:11:26 |
|  7 | Susan  | New Zealand |   580 | NULL         | 2015-01-29 02:11:57 |
+----+--------+-------------+-------+--------------+---------------------+

表格名称“scores”，我想找出每天谁是最佳球员？我是 mysql 的新手。因此，如果有人能告诉我在 mysql 中找出谁是每天最佳玩家的方法，将不胜感激!

我想达到的结果是每天只显示最好的玩家，如下所示:

+----+--------+-------------+-------+--------------+---------------------+
| id | name   | country     | score | email        | timestamp           |
+----+--------+-------------+-------+--------------+---------------------+
|  6 | Craig  | New Zealand |   980 | NULL         | 2015-01-29 02:11:26 |
|  3 | Peter  | USA         |   830 | NULL         | 2015-01-28 16:23:35 |
|  4 | Victor | New Zealand |   780 | NULL         | 2015-01-27 06:33:55 |
+----+--------+-------------+-------+--------------+---------------------+

提前致谢!

Answer 1

您将使用聚合和连接:

select t.*
from scores t join
     (select date(timestamp) as dte, max(score) as maxscore
      from scores t
      group by date(timestamp)
     ) tt
     on tt.dte = date(t.timestamp) and tt.maxscore = t.score;

注意:如果有重复项，这将返回所有具有相同最高分的人。