Wie kann man eine Spalte zu einem Abfrageergebnis "hinzufügen", wenn die Abfrage eine Aggregatfunktion enthält?

Question

Ich habe eine Tabelle mit dem Namen "Anwesenheit", die zur Erfassung der Anwesenheitszeit von Studenten in Kursen verwendet wird. Diese Tabelle hat 4 Spalten, nämlich 'id', 'course_id', 'attendance_time' und 'student_name'. Ein Beispiel für einige Datensätze in dieser Tabelle ist:

23 100 1/1/2010 10:00:00 Tom

24 100 1/1/2010 10:20:00 Bob

25 187 1/2/2010 08:01:01 Lisa

.....

Ich möchte eine Zusammenfassung der letzten Anwesenheitszeit für jeden Kurs erstellen. Ich habe die folgende Abfrage erstellt:

SELECT course_id, max(attendance_time) FROM attendance GROUP BY course_id

Das Ergebnis würde etwa so aussehen

100 1/1/2010 10:20:00

187 1/2/2010 08:01:01

Jetzt möchte ich nur noch die Spalte "id" zu dem obigen Ergebnis hinzufügen. Wie kann ich das tun?

Ich kann den Befehl nicht einfach in etwas wie dieses ändern

SELECT id, course_id, max(attendance_time) FROM attendance GROUP BY id, course_id

weil sie alle Datensätze zurückgeben würde, als ob die Aggregatfunktion nicht verwendet würde. Bitte helfen Sie mir.

Answer 1

Dies ist eine typische "greatest per group"-, "greatest-n-per-group"- oder "groupwise maximum"-Abfrage, die auf Stack Overflow fast jeden Tag auftaucht. Sie können Stack Overflow nach diesen Begriffen durchsuchen, um viele verschiedene Beispiele zu finden, wie man diese Aufgabe mit verschiedenen Datenbanken lösen kann. Eine Möglichkeit zur Lösung ist die folgende:

SELECT
    T2.course_id,
    T2.attendance_time
    T2.id
FROM (
    SELECT
        course_id,
        MAX(attendance_time) AS attendance_time
    FROM attendance
    GROUP BY course_id
) T1
JOIN attendance T2
ON T1.course_id = T2.course_id
AND T1.attendance_time = T2.attendance_time

Beachten Sie, dass diese Abfrage theoretisch mehrere Zeilen pro course_id zurückgeben kann, wenn es mehrere Zeilen mit der gleichen attendance_time gibt. Wenn dies nicht der Fall ist, brauchen Sie sich um dieses Problem nicht zu kümmern. Wenn dies ein potenzielles Problem ist, können Sie es lösen, indem Sie eine zusätzliche Gruppierung für course_id und attendance_time hinzufügen und die minimale oder maximale ID auswählen.

Answer 2

Ab SQL Server 2008 verwende ich gerne einen Common Table Expression, um aggregierte Spalten zu Abfragen hinzuzufügen:

WITH AttendanceTimes (course_id, maxTime)
AS 
(
    SELECT
        course_id,
        MAX(attendance_time)
    FROM attendance
    GROUP BY course_id
)

SELECT
    a.course_id,
    t.maxTime,
    a.id
FROM attendance a
INNER JOIN AttendanceTimes t
ON a.course_id = t.course_id

Answer 3

Wozu brauchen Sie die zusätzliche Spalte? Es gibt bereits eine Kurs-ID, die die Daten identifiziert. Eine synthetische ID in der Abfrage wäre nutzlos, da sie sich auf nichts bezieht. Wenn Sie den Höchstwert aus den Abfrageergebnissen für einen einzelnen Kurs erhalten möchten, können Sie eine Where-Bedingung wie diese hinzufügen:

SELECT course_id, max(attendance_time) FROM attendance GROUP BY course_id **WHERE course_id = your_id_here**;

Wenn Sie meinen, dass die Spalte "id" heißen soll, können Sie ihr in der Abfrage einen Alias geben:

SELECT course_id **AS id**, max(attendance_time) FROM attendance GROUP BY course_id;

Sie können aus Ihrer Abfrage eine Ansicht erstellen, um leicht auf die aggregierten Daten zuzugreifen:

CREATE VIEW max_course_times AS SELECT course_id AS id, max(attendance_time) FROM attendance GROUP BY course_id;

SELECT * FROM max_course_times;