Die oberste 1 Zeile jeder Gruppe erhalten

Question

Ich habe eine Tabelle, aus der ich den neuesten Eintrag für jede Gruppe abrufen möchte. Hier ist die Tabelle:

DocumentStatusLogs Tabelle

|ID| DocumentID | Status | DateCreated |
| 2| 1          | S1     | 7/29/2011   |
| 3| 1          | S2     | 7/30/2011   |
| 6| 1          | S1     | 8/02/2011   |
| 1| 2          | S1     | 7/28/2011   |
| 4| 2          | S2     | 7/30/2011   |
| 5| 2          | S3     | 8/01/2011   |
| 6| 3          | S1     | 8/02/2011   |

Die Tabelle wird gruppiert nach DocumentID und sortiert nach DateCreated in absteigender Reihenfolge. Für jede DocumentID Ich möchte den neuesten Stand erfahren.

Meine bevorzugte Ausgabe:

| DocumentID | Status | DateCreated |
| 1          | S1     | 8/02/2011   |
| 2          | S3     | 8/01/2011   |
| 3          | S1     | 8/02/2011   |

• Gibt es eine Aggregatfunktion, um nur die besten Ergebnisse aus jeder Gruppe zu erhalten? Siehe Pseudocode GetOnlyTheTop unten:

  SELECT
    DocumentID,
    GetOnlyTheTop(Status),
    GetOnlyTheTop(DateCreated)
  FROM DocumentStatusLogs
  GROUP BY DocumentID
  ORDER BY DateCreated DESC

• Wenn eine solche Funktion nicht existiert, gibt es dann eine Möglichkeit, die gewünschte Ausgabe zu erreichen?

• Oder könnte dies in erster Linie auf eine nicht normalisierte Datenbank zurückzuführen sein? Ich denke, da ich nur nach einer Zeile suche, sollte diese status auch in der übergeordneten Tabelle zu finden sein?

Weitere Informationen finden Sie in der übergeordneten Tabelle:

Aktuell Documents Tabelle

| DocumentID | Title  | Content  | DateCreated |
| 1          | TitleA | ...      | ...         |
| 2          | TitleB | ...      | ...         |
| 3          | TitleC | ...      | ...         |

Sollte die übergeordnete Tabelle so aussehen, dass ich leicht auf ihren Status zugreifen kann?

| DocumentID | Title  | Content  | DateCreated | CurrentStatus |
| 1          | TitleA | ...      | ...         | s1            |
| 2          | TitleB | ...      | ...         | s3            |
| 3          | TitleC | ...      | ...         | s1            |

アップデイト Ich habe gerade gelernt, wie man "Anwenden" benutzt, was es einfacher macht, solche Probleme zu lösen.

Answer 1

In Szenarien, in denen Sie die Verwendung von row_count() vermeiden wollen, können Sie auch einen Left Join verwenden:

select ds.DocumentID, ds.Status, ds.DateCreated 
from DocumentStatusLogs ds
left join DocumentStatusLogs filter 
    ON ds.DocumentID = filter.DocumentID
    -- Match any row that has another row that was created after it.
    AND ds.DateCreated < filter.DateCreated
-- then filter out any rows that matched 
where filter.DocumentID is null 

Für das Beispielschema können Sie auch eine "not in subquery" verwenden, die im Allgemeinen die gleiche Ausgabe wie die linke Verknüpfung ergibt:

select ds.DocumentID, ds.Status, ds.DateCreated 
from DocumentStatusLogs ds
WHERE ds.ID NOT IN (
    SELECT filter.ID 
    FROM DocumentStatusLogs filter
    WHERE ds.DocumentID = filter.DocumentID
        AND ds.DateCreated < filter.DateCreated)

Beachten Sie, dass das Subquery-Muster nicht funktionieren würde, wenn die Tabelle nicht mindestens einen einspaltigen eindeutigen Schlüssel/Constraint/Index hätte, in diesem Fall den Primärschlüssel "Id".

Beide Abfragen sind tendenziell "teurer" als die Abfrage row_count() (gemessen mit Query Analyzer). Es kann jedoch Szenarien geben, in denen sie schneller Ergebnisse liefern oder andere Optimierungen ermöglichen.

Answer 2

SELECT documentid, 
       status, 
       datecreated 
FROM   documentstatuslogs dlogs 
WHERE  status = (SELECT status 
                 FROM   documentstatuslogs 
                 WHERE  documentid = dlogs.documentid 
                 ORDER  BY datecreated DESC 
                 LIMIT  1)

Answer 3

Versuchen Sie dies:

SELECT [DocumentID]
    ,[tmpRez].value('/x[2]', 'varchar(20)') AS [Status]
    ,[tmpRez].value('/x[3]', 'datetime') AS [DateCreated]
FROM (
    SELECT [DocumentID]
        ,cast('<x>' + max(cast([ID] AS VARCHAR(10)) + '</x><x>' + [Status] + '</x><x>' + cast([DateCreated] AS VARCHAR(20))) + '</x>' AS XML) AS [tmpRez]
    FROM DocumentStatusLogs
    GROUP BY DocumentID
    ) AS [tmpQry]

Answer 4

Ich glaube, das kann man einfach so machen. Dies könnte einige Optimierungen erfordern, aber Sie können einfach das Maximum aus der Gruppe auswählen.

Diese Antworten sind ein Overkill.

SELECT
  d.DocumentID,
  MAX(d.Status),
  MAX(d1.DateCreated)
FROM DocumentStatusLogs d, DocumentStatusLogs d1
USING DocumentID
GROUP BY 1
ORDER BY 3 DESC