MySQL查询仅选择日期组的最后一行

Question

我有以下查询:

SELECT elo, date(date_calculated) date FROM users_historical_elo WHERE uid =36 order by id asc

产生这个结果:

|埃洛 |日期

|984|2011-04-04

|1010|2011-04-04

|1036|2011-04-04

|1016|2011-04-08

|1000|2011-04-08

|944|2011-04-09

|973|2011-04-09

...

我需要帮助来编写一个仅选择按日期分组的最后一个“elo”的查询。所以输出应该是:

|埃洛 |日期

|1036|2011-04-04

|1000|2011-04-08

|973|2011-04-09

...

谢谢!

Answer 1

如果 id 是表的主键，那么这样做:

SELECT elo
     , date(date_calculated) AS date
FROM users_historical_elo 
  JOIN
    ( SELECT MAX(id) AS id
      FROM users_historical_elo
      WHERE uid =36
      GROUP BY date(date_calculated)
    ) AS grp
    ON grp.id = users_historical_elo.id
ORDER BY grp.id ASC