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

Answer 2

尝试：

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

Answer 3

我认为你需要使用 MAX 和 GROUP By。请参阅http://www.tutorialspoint.com/mysql/mysql-max-function。 htm

我认为这会起作用

SELECT elo, MAX(date(date_calculated)) date 
FROM users_historical_elo 
WHERE uid =36 
GROUP BY elo 
ORDER BY id asc

Answer 4

这：

 SELECT elo, MAX(date) FROM users_historical_elo

会返回 elo 和任何 elo 的最后日期，这似乎是您所要求的？