SQL-Abfrageverbesserung - Select mit max und groupby

Question

Problème

---

In den folgenden beiden Tabellen möchte ich alle Kennungen für Beiträge auswählen, deren jüngster (d. h. letzter) Kommentar in der angegebenen Zeitspanne (z. B. Februar 2010) abgegeben wurde.

Das Ergebnis der Abfrage sollte nur die Beitrags-ID 1 zurückgeben, da der jüngste Kommentar für die Beitrags-ID 2 außerhalb des Bereichs des Zeitspannenfilters liegt.

Question

---

Ich habe die SELECT Anweisung, die korrekt zu sein scheint und mit allen Testfällen zurechtkommt.

In dem Bestreben, meine SQL-Kenntnisse weiter zu verbessern, frage ich die Community, ob es eine "bessere" Methode für dieses Szenario gibt, Vorschläge zur Verbesserung der vorhandenen Anweisung und/oder Randfälle, die nicht abgedeckt sind.

Beachten Sie, dass dies eine lose Übersetzung der tatsächlichen Tabellen ist, die mit der Absicht geändert wurde, die Frage leichter verständlich zu machen. Ich verwende SQL Server 2005, was das betrifft.

Tische

---

Beitrag

Id    Text     Visible
1     Post 1   1
2     Post 2   1
3     Post 3   0
.     ...
n     Post n   1

Kommentar

Id    Post_Id    Text                  CommentNumber    Timestamp
1     1          Comment 1, Post 1     1                2/3/2010
2     1          Comment 2, Post 1     2                2/4/2010
3     2          Comment 1, Post 2     1                3/1/2010
.     .          .
n     m          Comment n, Post m     x                xx/xx/xxxx

SQL-Befehl

---

SELECT [Id],[Text]
FROM [Post]  
WHERE [Id] IN (  
    SELECT comment1.[Post_Id]  
    FROM (  
        SELECT max([CommentNumber]) as maxComment,  
            [Post_id]  
        FROM [Comment]  
        GROUP BY [Post_id]  
    ) as comment2  
    INNER JOIN [Comment] as comment1 on comment1.[Post_id] = comment2.[Post_id]  
    WHERE comment1.[Timestamp] BETWEEN '2/1/2010 00:00:00.000' AND '2/28/2010 23:59:59.999'  
    AND comment1.[CommentNumber] = comment2.maxComment  
)
AND [Post].[Visible] = 1

Bonusfrage

---

Ist es möglich, diese Abfrage mit NHiberate zu erstellen (entweder mit der Criteria API oder mit HQL)?

Answer 1

SELECT
    Post_Id
FROM
    Comment
GROUP BY
    Post_Id
HAVING
    MAX(Timestamp) >= '2/1/2010'

Denken Sie an HAVING als WHERE die stattfindet nach GROUP BY , die auf dem gruppiert Ergebnismenge.

Ich weiß allerdings nicht, wie es mit NHibernate aussieht.

Answer 2

Es wurden bereits gute Lösungen gepostet, aber ich dachte, ich würde eine Erklärung posten, wie Ihre Anfrage Schritt für Schritt vereinfacht werden kann:

Die äußerste Unterabfrage ist redundant

Der äußerste Teil der Unterabfrage (die SELECT [Id] FROM [Post] WHERE [Id] IN ( bit) ist überflüssig, da Sie bereits eine Liste von Ids zurückgeben).

Damit bleibt uns

SELECT comment1.[Post_Id]
FROM (  
    SELECT max([CommentNumber]) as maxComment,  
        [Post_id]  
    FROM [Comment]  
    GROUP BY [Post_id]  
) as comment2  
INNER JOIN [Comment] as comment1 on comment1.[Post_id] = comment2.[Post_id]  
WHERE comment1.[Timestamp] BETWEEN '2/1/2010 00:00:00.000' AND '2/28/2010 23:59:59.999'  
AND comment1.[CommentNumber] = comment2.maxComment  

Die Verwendung von CommentNumber ist überflüssig

Es besteht keine Notwendigkeit, CommentNumber zu verwenden, um den neuesten Kommentar zu erhalten, da die Beiträge bereits nach Zeitstempel geordnet sind. Das bedeutet, dass wir nicht den Zeitstempel des Kommentars mit der höchsten Id auswählen müssen, sondern einfach den höchsten Zeitstempel auswählen können.

Damit entfällt die Notwendigkeit, sich erneut mit den Kommentaren zu verbinden, so dass wir mit:

SELECT [Post_Id], SomeColumn, SomeOtherColumn
FROM (
    SELECT max([TimeStamp]) as maxTimeStamp,
        [Post_id],
        SomeColumn,
        SomeOtherColumn
    FROM [Comment]
    GROUP BY [Post_id]
) as GroupedComments
WHERE GroupedComments.maxTimeStamp BETWEEN '2/1/2010 00:00:00.000' AND '2/28/2010 23:59:59.999'

Die Unterabfrage ist jetzt überflüssig

Nun, da die Abfrage etwas vereinfacht wurde, sollte es einfach sein zu sehen, wie sie weiter auf eine der anderen hier veröffentlichten Lösungen reduziert werden kann, indem die distinct o having Syntax.

Verwenden Sie < und >= anstelle von BETWEEN

Nur ein kleiner Kritikpunkt. Anstatt mühsam nach dem letzten Datum im Februar zu suchen, macht die Aufteilung des BETWEEN in ein < und ein >= die Abfrage viel sauberer:

WHERE GroupedComments.maxTimeStamp >= '2/1/2010'
AND GroupedComments.maxTimeStamp < '3/01/2010'

Answer 3

Dies dürfte etwas schneller sein als die Verwendung der HAVING-Klausel.

select distinct Post_id from Comment
where Timestamp >= '2/1/2010';

Answer 4

Dies ist die Anfrage, auf die ich derzeit abziele, nachdem ich AakashMs und Kragens Antworten kombiniert habe:

SELECT [Id],[Text]
From [Post]
WHERE [Id] IN (
    SELECT Post_Id
    FROM Comment
    GROUP BY Post_Id
    HAVING MAX(Timestamp) >= '3/1/2010' AND MAX(Timestamp) < '4/1/2010'
)
AND [Post].[Visible] = 1

---

Im Folgenden wird beschrieben, wie diese Abfrage in NHibernate unter Verwendung der Criteria-API dargestellt werden kann:

var subCriteria = DetachedCriteria.For<Comment>()
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.GroupProperty("Post.Id")))
    .Add(Restrictions.Ge("Timestamp", new DateTime(2010, 3, 1)))
    .Add(Restrictions.Lt("Timestamp", new DateTime(2010, 4, 1)));

var criteria = session.CreateCriteria<Post>()
    .Add(Restrictions.Eq("Visible", true))
    .Add(Subqueries.PropertyIn("Id", subCriteria));