Wybierz wiersz z najnowszą datą dla każdego użytkownika
Mam tabelę ("lms_attendance") czasów zameldowania i wymeldowania użytkowników, która wygląda tak:
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
Próbuję stworzyć Widok tej tabeli, który wyświetli tylko najnowszy rekord na id użytkownika, dając mi wartość " in " lub "out", więc coś w stylu:
id user time io
2 9 1370931664 out
3 6 1370932128 out
5 12 1370933037 in
Jak na razie jestem blisko, ale zdałem sobie sprawę, że poglądy nie akceptują subquery, co znacznie utrudnia. Najbliższe zapytanie jakie otrzymałem to:
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`
Ale to co dostaję to:
id user time io
3 6 1370932128 out
1 9 1370931664 out
5 12 1370933037 in
4 12 1370932128 out
Który jest blisko, ale nie idealnie. Wiem, że ostatnia grupa by nie powinna tam być, ale bez niej zwraca ostatni czas, ale nie z jego względną wartością IO.
Jakieś pomysły? Dzięki!8 answers
Zapytanie:
SELECT t1.*
FROM lms_attendance t1
WHERE t1.time = (SELECT MAX(t2.time)
FROM lms_attendance t2
WHERE t2.user = t1.user)
Wynik:
| ID | USER | TIME | IO |
--------------------------------
| 2 | 9 | 1370931664 | out |
| 3 | 6 | 1370932128 | out |
| 5 | 12 | 1370933037 | in |
Rozwiązanie, które będzie działać za każdym razem:
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)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-06-11 07:50:35
Nie trzeba próbować odkrywać koła na nowo, ponieważ jest to powszechnynajwiększy-N-na-Grupę problem . Bardzo ładne rozwiązanie jest przedstawione .
Preferuję Najbardziej uproszczone rozwiązanie (patrz sqlfiddle, zaktualizowane) bez subqueries (dzięki temu łatwe w użyciu w widokach):
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
Działa to również w przypadku, gdy istnieją dwa różne rekordy o tej samej największej wartości w tej samej grupie - dzięki sztuczce z (t1.time = t2.time AND t1.Id < t2.Id)
. Wszystko, co tu robię, to zapewnienie, że w przypadek, gdy dwa rekordy tego samego Użytkownika mają ten sam czas, wybierany jest tylko jeden. Nie ma znaczenia, czy kryteria są Id
, czy coś innego - w zasadzie wszelkie kryteria, które są gwarantowane, że będą unikalne, sprawią, że praca tutaj będzie.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:18:22
Bazując na odpowiedzi @TMS, podoba mi się, ponieważ nie ma potrzeby tworzenia subqueries, ale myślę, że ommit {[2] } część będzie wystarczająca i znacznie prostsza do zrozumienia i przeczytania.
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
Jeśli nie interesują Cię wiersze z NULL times, możesz je filtrować w klauzuli 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-03-16 18:35:49
Już rozwiązane, ale dla przypomnienia, innym podejściem byłoby stworzenie dwóch widoków...
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;
Kliknij tutaj, aby zobaczyć go w akcji w SQL Fiddle
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-03-23 09:34:06
Spróbuj tego zapytania:
select id,user, max(time), io
FROM lms_attendance group by user;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-09-22 05:38:04
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-06-11 07:18:18
Możliwe, że możesz grupować według użytkownika, a następnie zamawiać według czasu desc. Coś jak poniżej
SELECT * FROM lms_attendance group by user order by time desc;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-06-11 07:17:03
To mi pomogło:
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-12-13 21:59:37