Wählen Sie die Zeile mit dem letzten Datum pro Benutzer

Ich habe eine Tabelle ("lms_attendance") der Benutzer " check-in-und out-Zeiten, die wie folgt aussieht:

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

Ich versuche zu schaffen, einen Blick auf diese Tabelle, würde die Ausgabe nur den aktuellsten Datensatz pro Benutzer-id, während er mir die "in" oder "out" - Wert, so etwas wie:

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

Ich bin mir ziemlich nah, so weit, aber ich habe gemerkt, dass die Ansichten nicht akzeptieren subquerys, das macht es viel schwieriger. Die nächste Abfrage, die ich bekam, war :

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`

Aber was ich bekomme ist :

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

Das ist in der Nähe, aber nicht perfekt. Ich weiß, dass die Letzte Gruppe, indem Sie die dort nicht sein sollen, aber ohne es, gibt es die meisten neueren Zeit, aber nicht mit seiner relativen IO Wert.

Irgendwelche Ideen?
Danke!

möglich, Duplikat der so wählen Sie den letzten Satz von datierten Aufzeichnungen aus einer mysql-Tabelle
Gehen Sie zurück zu der Anleitung. Sie werden sehen, dass es bietet Lösungen für dieses problem sowohl mit als auch ohne (korreliert und uncorelated) Unterabfragen.
technisch, wie ich wies in meiner Antwort, dies ist ein Duplikat der alle 700 Fragen mit dem greatest-n-pro-Gruppe - tag.
was ist 'io (enum)'?
Ich hatte eine Spalte namens "IO", das steht für "in" oder "out", es war eine enum-Typs mit möglichen Werten "in" oder "out". Dies wurde verwendet, um zu verfolgen, wenn die Menschen aktiviert in die und aus der a-Klasse.

InformationsquelleAutor Keith | 2013-06-11

169

Abfrage:

SQLFIDDLEExample
```
SELECT t1.*
FROM lms_attendance t1
WHERE t1.time = (SELECT MAX(t2.time)
                 FROM lms_attendance t2
                 WHERE t2.user = t1.user)
```
Ergebnis:
```
| ID | USER |       TIME |  IO |
--------------------------------
|  2 |    9 | 1370931664 | out |
|  3 |    6 | 1370932128 | out |
|  5 |   12 | 1370933037 |  in |
```
Lösung, die funktionieren würde 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)
```
wow!!! nicht nur, dass diese Arbeit, durfte ich eine Ansicht erstellen mit dieser Abfrage, obwohl es enthält Unterabfragen. vor, als ich versuchte, eine Sicht erstellen, die mit Unterabfragen, es ließ mich nicht. gibt es Regeln, warum das erlaubt ist aber ein anderes nicht?
Ich weiß es nicht 🙂 wahrscheinlich etwas falsch war mit Tabellen-alias...
sehr seltsam. Dank einer Tonne! vielleicht war es, weil meine subquery war eine pseudo-Tabelle, die ich wurde der Auswahl AUS, wobei in diesem Beispiel die in der WHERE-Klausel.
Keine Notwendigkeit für Unterabfragen! Außerdem ist diese Lösung nicht funktioniert, wenn es zwei Datensätze mit exakt der gleichen Zeit. Es gibt keine Notwendigkeit, zu versuchen, das Rad neu erfinden jeder Zeit an, wie dieses gemeinsame problem - stattdessen gehen Sie für bereits getestete und optimierte Lösungen - @Prodikl siehe meine Antwort.
Diese Lösung funktioniert, wenn die Sätze haben genau die gleiche Zeit, da die Abfrage ist die Lokalisierung der Datensatz mit der größten id. Dies bedeutet, dass die Zeit in der Tabelle ist die Einfügung Zeit, die nicht eine gute Annahme. Ihre Lösung anstelle vergleicht die Zeitstempel und, wenn zwei Zeitstempel identisch sind, kehren Sie die Zeile mit der größten id. Daher, Ihre Lösung wird auch davon ausgegangen, dass der timestamp in dieser Tabelle ist im Zusammenhang mit der Bestellung der insertion, die ist der größte Fehler mit beiden Abfragen.

InformationsquelleAutor Justin
66

Keine Notwendigkeit, zu versuchen, das Rad neu zu erfinden, wie dies üblich ist größte-n-pro-Gruppe-problem. Sehr schöne Lösung präsentiert wird.

Ich lieber die simple Lösung (sehen SQLFiddle, aktualisiert, Justin) ohne Unterabfragen (so einfach zu bedienen, Ansichten):
```
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
```
Funktioniert das auch in einem Fall, wo es gibt zwei verschiedene Datensätze mit der gleichen höchsten Wert innerhalb der gleichen Gruppe - Dank den trick mit (t1.time = t2.time AND t1.Id < t2.Id). Alles was ich hier mache ist zu gewährleisten, dass im Fall, wenn zwei Datensätze den gleichen Benutzer haben gleichen Zeit nur einer gewählt werden. Eigentlich auch egal, wenn die Kriterien Id oder etwas anderes - im Grunde alle Kriterien, die garantiert einzigartig machen würde, den job hier.

Die max verwendet t1.time < t2.time und die min wäre t1.time > t2.time was ist das Gegenteil von meiner ersten intuition.
da gibt es die implizite negation versteckt: wählen Sie alle Datensätze aus t1, die nicht entsprechenden Datensatz aus der t2, wo die t1.time < t2.time Bedingung gilt 🙂
WHERE t2.user IS NULL ist ein bisschen seltsam. Welche Rolle spielt dieses Online Spiel?
Die akzeptierte Antwort, geschrieben von Justin ist, kann mehr optimal. Die akzeptierte Antwort verwendet eine rückwärts-index-scan auf dem primary-key der Tabelle, gefolgt von einer Begrenzung, gefolgt von einem Sequenz-scan der Tabelle. Daher ist die akzeptierte Antwort kann erheblich optimiert mit einem zusätzlichen index. Diese Abfrage kann optimiert werden durch einen index, wie gut, wie es führt zwei Sequenz-scans, aber auch ein hash und ein "hash-anti-join", der die Ergebnisse der scan-Sequenz und der hash-Wert von den anderen Reihenfolge Scannen. Ich wäre daran interessiert, eine Erläuterung, welcher Ansatz wirklich mehr optimal.
könnten Sie bitte klären OR (t1.time = t2.time AND t1.Id < t2.Id)) Abschnitt?

InformationsquelleAutor TMS
5

Sitz in @TMS Antwort, ich mag es, weil es keine Notwendigkeit für Unterabfragen, aber ich denke, ommiting die 'OR' Teil ausreichend und viel einfacher zu verstehen und zu Lesen.
```
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
```
wenn Sie nicht interessiert sind in Zeilen mit null-Zeiten Sie können die filter in der WHERE Klausel:
```
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
```
Das weglassen der OR Teil ist eine wirklich schlechte Idee, wenn zwei Datensätze haben die gleiche time.
Anf was ist Leistung? Es scheint zu erstellen, die n*n-Tabelle.
Ich möchte vermeiden, diese Lösung für performance-Gründen. @OlegKuts bereits erwähnt, dies wird sehr langsam auf mittleren bis großen Datenmengen.

InformationsquelleAutor user1792210

Schon gelöst, aber nur für die Aufzeichnung, ein anderer Ansatz zum erstellen von zwei Ansichten...

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;

Klicken Sie hier, um zu sehen, Sie in Aktion auf SQL Fiddle

vielen Dank für das follow-up! ja, ich würde erstellen Sie mehrere Ansichten, wenn es nicht einen einfacheren Weg. nochmals vielen Dank

InformationsquelleAutor davmos

2

Versuchen, diese Abfrage:
```
  select id,user, max(time), io 
  FROM lms_attendance group by user;
```
Versuchen Sie, eine SQLFiddle dieser. Sie werden wahrscheinlich feststellen, dass id und io sind nonaggregated Spalten, die nicht verwendet werden group by.
es gibt keine Garantie-id wird die id mit max(Zeit), es könnte einer der ids innerhalb der Gruppe. das ist das problem ich kam hier, um zu lösen, die noch auf der Suche

InformationsquelleAutor Sugan

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

danke. ich weiß, ich kann es mit einer Unterabfrage, aber ich hatte gehofft, diesen in eine Ansicht, und es nicht zulassen, dass Unterabfragen in Aussicht, soweit ich weiß. würde ich haben, um jeden sub-Abfrage in eine view, etc.?
join (select * from lms_attendance ) b = join lms_attendance b

InformationsquelleAutor chetan

 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

InformationsquelleAutor Konstantin XFlash Stratigenas

-2

Möglicherweise können Sie der Gruppe Benutzer und dann order by Zeit desc. Sowas wie unten
```
  SELECT * FROM lms_attendance group by user order by time desc;
```
InformationsquelleAutor user2365199

-3

Dieser arbeitete für mich:

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

InformationsquelleAutor Alvaro Sifuentes

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.