选择每个用户最近日期的行

Question

我有一个用户签入和签出时间的表（“lms_attendance”），如下所示：

 id  user    time    io (enum)
1   9   1370931202  out
2   9   1370931664  out
3   6   1370932128  out
4   12  1370932128  out
5   12  1370933037  in

我正在尝试创建此表的视图，该视图将仅输出每个用户 ID 的最新记录，同时为我提供“输入”或“输出”值，例如：

 id  user    time    io
2   9   1370931664  out
3   6   1370932128  out
5   12  1370933037  in

到目前为止我已经很接近了，但我意识到视图不会接受子查询，这使得它变得更加困难。我得到的最接近的查询是：

 select
    `lms_attendance`.`id` AS `id`,
    `lms_attendance`.`user` AS `user`,
    max(`lms_attendance`.`time`) AS `time`,
    `lms_attendance`.`io` AS `io`
from `lms_attendance`
group by
    `lms_attendance`.`user`,
    `lms_attendance`.`io`

但我得到的是：

 id  user    time    io
3   6   1370932128  out
1   9   1370931664  out
5   12  1370933037  in
4   12  1370932128  out

这很接近，但并不完美。我知道最后一个 group by 不应该在那里，但是没有它，它会返回最近的时间，而不是它的相对 IO 值。

有任何想法吗？谢谢！

原文由 Keith 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

询问：

SQLFIDDLE示例

SELECT t1.*
FROM lms_attendance t1
WHERE t1.time = (SELECT MAX(t2.time)
                 FROM lms_attendance t2
                 WHERE t2.user = t1.user)

结果：

 | ID | USER |       TIME |  IO |
--------------------------------
|  2 |    9 | 1370931664 | out |
|  3 |    6 | 1370932128 | out |
|  5 |   12 | 1370933037 |  in |

请注意，如果用户有多个具有相同“最大”时间的记录，则上述查询将返回多条记录。如果您只希望每个用户有 1 条记录，请使用以下查询：

SQLFIDDLE示例

SELECT t1.*
FROM lms_attendance t1
WHERE t1.id = (SELECT t2.id
                 FROM lms_attendance t2
                 WHERE t2.user = t1.user
                 ORDER BY t2.id DESC
                 LIMIT 1)

原文由 Justin 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

我有一张非常大的桌子，这里的所有其他建议都需要很长时间才能执行。我想出了这个更快的hacky方法。缺点是，如果 max(date) 行有该用户的重复日期，它将返回它们。

 SELECT * FROM mb_web.devices_log WHERE CONCAT(dtime, '-', user_id) in (
    SELECT concat(max(dtime), '-', user_id) FROM mb_web.devices_log GROUP BY user_id
)

原文由 I wrestled a bear once. 发布，翻译遵循 CC BY-SA 4.0 许可协议