mysql - 获取 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

您正在使用 group by 的 MySQL 扩展，文档明确警告不要这样做。该扩展引用了 select 中不在 group by 中的列。该值是任意的。请参阅here .

以下是重写查询的方法:

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