获取MySQL中的最后一行

Question

我想从下表中检索每个用户的最后一次体验。

| ID | NAME | FLAG |                     CREATED_AT |
|----|------|------|--------------------------------|
|  1 | John |    1 | January, 15 2014 00:00:00+0000 |
|  2 | Mary |    1 | January, 16 2014 00:00:00+0000 |
|  3 | John |    0 | January, 17 2014 00:00:00+0000 |
|  4 | John |    1 | January, 18 2014 00:00:00+0000 |
|  5 | John |    0 | January, 19 2014 00:00:00+0000 |
|  6 | Mary |    0 | January, 20 2014 00:00:00+0000 |
|  7 | John |    1 | January, 21 2014 00:00:00+0000 |

正确的结果应该是(顺序不重要)：

| NAME | FLAG |                MAX(CREATED_AT) |
|------|------|--------------------------------|
| John |    1 | January, 21 2014 00:00:00+0000 |
| Mary |    0 | January, 20 2014 00:00:00+0000 |

但我的结果是(玛丽的旗子不对)：

| NAME | FLAG |                MAX(CREATED_AT) |
|------|------|--------------------------------|
| John |    1 | January, 21 2014 00:00:00+0000 |
| Mary |    1 | January, 20 2014 00:00:00+0000 |

我使用的查询是：

SELECT name, flag, max(created_at)
FROM logs
GROUP BY name

我遗漏了什么？

Answer 1

您正在使用文档显式警告的MySQL扩展到group by。扩展指的是select中没有在group by中的列。这个值是任意的。见这里。

下面是重写查询的一种方法：

SELECT name, substring_index(group_concat(flag order by created_at desc), ',', 1) as flag, max(created_at)
FROM logs
GROUP BY name;