Выберите строку с самой последней датой для каждого пользователя

У меня есть таблица ( "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

Я пытаюсь создать представление этой таблицы, которое будет выводить только самую последнюю запись на идентификатор пользователя, давая мне значение "in" или "out", поэтому что-то вроде:

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

Это близко, но не идеально. Я знаю, что последняя группа не должна быть там, но без нее она возвращает самое последнее время, но не с относительным значением IO.

Любые идеи? Спасибо!

Ответ 1

Query:

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 |

Решение, которое будет работать каждый раз:

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

Не нужно пытаться изобретать колесо, поскольку это обычная проблема наибольшая-n-на-группу. Очень приятное решение представлено.

Я предпочитаю наиболее упрощенное решение (см. SQLFiddle, обновленный Justin's) без подзапросов (таким образом, легко использовать в представлениях):

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

Это также работает в случае, когда в одной группе есть две разные записи с одинаковым наибольшим значением - благодаря трюку с (t1.time = t2.time AND t1.Id < t2.Id). Все, что я здесь делаю, заключается в том, чтобы убедиться, что в случае, когда две записи одного и того же пользователя имеют одинаковое время, выбирается только один. На самом деле не имеет значения, если критерии Id или что-то еще - в принципе любые критерии, которые гарантированно будут уникальными, сделают здесь работу.

Ответ 3

На основе ответа @TMS мне это нравится, потому что нет необходимости в подзапросах, но я думаю, что ommiting часть 'OR' будет достаточной и намного проще понять и прочитать.

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

если вас не интересуют строки с нулевым временем, вы можете отфильтровать их в предложении 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

Уже решена, но только для записи другой подход состоял бы в создании двух представлений...

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;

Нажмите здесь, чтобы увидеть его в действии на SQL Fiddle

Ответ 5

Попробуйте этот запрос:

  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

 select result from (
     select vorsteuerid as result, count(*) as anzahl from kreditorenrechnung where kundeid = 7148
     group by vorsteuerid
 ) a order by anzahl desc limit 0,1

Ответ 8

Хорошо, это может быть либо взлом, либо подвержен ошибкам, но как-то это работает как well-

SELECT id, MAX(user) as user, MAX(time) as time, MAX(io) as io FROM lms_attendance GROUP BY id;

Ответ 9

Возможно, вы можете сделать группу по пользователю, а затем упорядочить по времени desc. Что-то вроде ниже

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

Ответ 10

Это сработало для меня:

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