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

问题描述

我有一个用户入住和退房时间的表格(lms_attendance")，如下所示:

I have a table ("lms_attendance") of users' check-in and out times that looks like this:

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 的最新记录，同时为我提供输入"或输出"值，例如:

I'm trying to create a view of this table that would output only the most recent record per user id, while giving me the "in" or "out" value, so something like:

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

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

I'm pretty close so far, but I realized that views won't accept subquerys, which is making it a lot harder. The closest query I got was :

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 值.

Which is close, but not perfect. I know that last group by shouldn't be there, but without it, it returns the most recent time, but not with it's relative IO value.

有什么想法吗?谢谢！

推荐答案

查询:

SQLFIDDLEExample

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 |

每次都有效的解决方案:

Solution which gonna work everytime:

SQLFIDDLEExample

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)

这篇关于选择每个用户最近日期的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！