Warum sind meine MySQL SUM()-Ergebnisse beim Zusammenführen mehrerer Tabellen falsch?

MySQL SUM() und Multi-Table-Joins: Vermeidung falscher Aggregationen

Das Kombinieren von SUM() Berechnungen mit mehreren Tabellenverknüpfungen in MySQL erfordert sorgfältige Überlegungen, um ungenaue Ergebnisse zu vermeiden. Eine häufige Gefahr entsteht durch den kartesischen Produkteffekt bei Verknüpfungen, der zu überhöhten Beträgen führt.

Das Problem: Überhöhte SUM()-Ergebnisse

Ein Benutzer hat versucht, zwei Abfragen, die jeweils Summen aus unterschiedlichen Tabellen berechnen, in einer einzigen verbundenen Abfrage zusammenzufassen.

Abfrage 1 (Kilometerstand): Summiert die Fahrzeit (in Minuten) pro Woche, gruppiert nach Lehrer.

<code class="language-sql">SELECT last_name, first_name, ..., SUM(drive_time) AS MINUTES
FROM bhds_mileage ...
WHERE mil_date BETWEEN ... AND ...
GROUP BY ...</code>

Abfrage 2 (Zeitkarte): Summe der Gesamtstunden pro Woche, gruppiert nach Lehrer.

<code class="language-sql">SELECT last_name, first_name, ..., SUM(tm_hours) AS total
FROM bhds_timecard ...
WHERE tm_date BETWEEN ... AND ...
GROUP BY ...</code>

Beitrittsversuch fehlgeschlagen:

Der Versuch des Benutzers, diese Abfragen direkt zu kombinieren, führte zu falschen Summen:

<code class="language-sql">SELECT last_name, first_name, ..., SUM(tm_hours) AS total, SUM(drive_time) AS MINUTES
FROM bhds_timecard ...
LEFT JOIN bhds_mileage ...
ON ...
WHERE ...
GROUP BY ...</code>

Das Problem? Die SUM()-Funktionen wurden nach der Verknüpfung angewendet, was aufgrund der Möglichkeit mehrerer übereinstimmender Zeilen zwischen den verbundenen Tabellen zur Multiplikation von Summen führte.

Die Lösung: Voraggregierte Unterabfragen

Der richtige Ansatz besteht darin, die Summen vor dem Beitritt in Unterabfragen vorab zu aggregieren:

<code class="language-sql">SELECT last_name, first_name, ..., total, minutes
FROM bhds_teachers ...
LEFT JOIN (
    -- Subquery 1: Mileage SUM
    SELECT teacher_id, SUM(drive_time) AS minutes, ...
    FROM bhds_mileage ...
    WHERE mil_date BETWEEN ... AND ...
    GROUP BY teacher_id
) AS m ON ...
LEFT JOIN (
    -- Subquery 2: Timecard SUM
    SELECT teacher_id, SUM(tm_hours) AS total, ...
    FROM bhds_timecard ...
    WHERE tm_date BETWEEN ... AND ...
    GROUP BY teacher_id
) AS t ON ...</code>

Durch die Ausführung der SUM() Vorgänge in separaten Unterabfragen stellen wir sicher, dass die Daten jeder Tabelle korrekt aggregiert werden, bevor die Verknüpfung erfolgt, wodurch die Multiplikation von Summen verhindert und genaue Ergebnisse erzielt werden. Das teacher_id (oder ein Äquivalent) ist entscheidend für die korrekte Join-Bedingung.

Das obige ist der detaillierte Inhalt vonWarum sind meine MySQL SUM()-Ergebnisse beim Zusammenführen mehrerer Tabellen falsch?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!