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

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

我有一个用户签到和退出时间的表格(“lms_考勤”)，看起来是这样的:

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

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的最新记录，同时给出“in”或“out”值，比如:

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 :

到目前为止，我已经非常接近了，但是我意识到视图不会接受subquerys，这使它变得更加困难。我得到的最接近的问题是:

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`

But what I get is :

但我得到的是:

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

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.

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

Any ideas? Thanks!

什么好主意吗?谢谢!

8 个解决方案

#1

Query:

查询:

SQLFIDDLEExample

SQLFIDDLEExample

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

Result:

结果:

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

Solution which gonna work everytime:

解决方案每次都会奏效:

SQLFIDDLEExample

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)

#2

No need to trying reinvent the wheel, as this is common greatest-n-per-group problem. Very nice solution is presented.

不需要尝试重新发明*，因为这是每个组中最常见的最大的问题。给出了一个很好的解。

I prefer the most simplistic solution (see SQLFiddle, updated Justin's) without subqueries (thus easy to use in views):

我更喜欢最简单的解决方案(参见SQLFiddle，更新了Justin的)，没有子查询(因此在视图中很容易使用):

SELECT t1.*
FROM lms_attendance AS t1
LEFT OUTER JOIN lms_attendance AS t2
  ON t1.user = t2.user 
        AND (t1.time < t2.time 
         OR (t1.time = t2.time AND t1.Id < t2.Id))
WHERE t2.user IS NULL

This also works in a case where there are two different records with the same greatest value within the same group - thanks to the trick with (t1.time = t2.time AND t1.Id < t2.Id). All I am doing here is to assure that in case when two records of the same user have same time only one is chosen. Doesn't actually matter if the criteria is Id or something else - basically any criteria that is guaranteed to be unique would make the job here.

这也适用于在同一组中有两个不同的记录具有相同的最大值的情况，这要归功于(t1)。时间= t2。时间和t1。Id < t2.Id)。我在这里所做的只是确保，如果同一用户的两个记录同时只有一个记录被选择。实际上，不管标准是Id还是其他什么——基本上，任何被保证是唯一的标准都可以在这里工作。

#3

Based in @TMS answer, I like it because there's no need for subqueries but I think ommiting the 'OR' part will be sufficient and much simpler to understand and read.

基于@TMS的回答，我喜欢它，因为它不需要子查询，但我认为省略“或”部分就足够了，更容易理解和阅读。

SELECT t1.*
FROM lms_attendance AS t1
LEFT JOIN lms_attendance AS t2
  ON t1.user = t2.user 
        AND t1.time < t2.time
WHERE t2.user IS NULL

if you are not interested in rows with null times you can filter them in the WHERE clause:

如果您对具有空时间的行不感兴趣，可以在WHERE子句中过滤它们:

SELECT t1.*
FROM lms_attendance AS t1
LEFT JOIN lms_attendance AS t2
  ON t1.user = t2.user 
        AND t1.time < t2.time
WHERE t2.user IS NULL and t1.time IS NOT NULL

#4

Already solved, but just for the record, another approach would be to create two views...

已经解决了，但是只是为了记录，另一个方法是创建两个视图…

CREATE TABLE lms_attendance
(id int, user int, time int, io varchar(3));

CREATE VIEW latest_all AS
SELECT la.user, max(la.time) time
FROM lms_attendance la 
GROUP BY la.user;

CREATE VIEW latest_io AS
SELECT la.* 
FROM lms_attendance la
JOIN latest_all lall 
    ON lall.user = la.user
    AND lall.time = la.time;

INSERT INTO lms_attendance 
VALUES
(1, 9, 1370931202, 'out'),
(2, 9, 1370931664, 'out'),
(3, 6, 1370932128, 'out'),
(4, 12, 1370932128, 'out'),
(5, 12, 1370933037, 'in');

SELECT * FROM latest_io;

Click here to see it in action at SQL Fiddle

点击这里查看SQL Fiddle

#5

Try this query:

试试这个查询:

  select id,user, max(time), io 
  FROM lms_attendance group by user;

#6

select b.* from 

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

join

    (select * 
    from `lms_attendance` ) b

on a.user = b.user
and a.time = b.time

#7

Possibly you can do group by user and then order by time desc. Something like as below

可能你可以按用户分组，然后按时间顺序进行排序，如下所示。

  SELECT * FROM lms_attendance group by user order by time desc;

#8

This worked for me:

这工作对我来说:

SELECT user, time FROM 
(
    SELECT user, time FROM lms_attendance --where clause
) AS T 
WHERE (SELECT COUNT(0) FROM table WHERE user = T.user AND time > T.time) = 0
ORDER BY user ASC, time DESC