mysql - 如何从 Mysql 中获取最新的行

Question

我有下面提到的表格：

ID      Date                    Value
AL-1    2017-04-01 22:04:08     154
AL-1    2017-04-05 15:08:45     158
AL-1    2017-04-06 18:09:15     225
AL-1    2017-04-08 20:08:17     254
AL-2    2017-04-01 22:04:08     154

我正在尝试这个：select ID, Value from table1 where id in ('AL-1','AL-2') and group by max(date(Date));但它给了我这样的输出：

ID      Date                    Value
AL-1    2017-04-01 22:04:08     154
AL-2    2017-04-01 22:04:08     154

期望的输出：

ID      Date                    Value
AL-1    2017-04-08 20:08:17     254
AL-2    2017-04-01 22:04:08     154

标签： mysqlsql

Answer 1

有一个子查询，它返回每个 id 及其最大日期。JOIN结果：

select t1.ID, t1.Value, t1.date
from table1 t1
join (select id, max(Date) maxdate
      from table1
      where id in ('AL-1','AL-2')
      group by id) t2 on t1.ID = t2.ID and date(t1.Date) = date(t2.maxdate)
where t1.id in ('AL-1','AL-2')