Wie vermeide ich multiplizierte SUM-Werte beim Verknüpfen von Tabellen mit Aggregatfunktionen in MySQL?

Tipps zur Vermeidung der Multiplikation von SUM-Werten bei der Verwendung von Aggregatfunktionen zum Verknüpfen von Tabellen in MySQL

Beim Kombinieren mehrerer Join-Vorgänge in einer MySQL-Abfrage kann die Verwendung von Aggregatfunktionen wie SUM zu unerwarteten Ergebnissen führen. Dies liegt daran, dass das kartesische Zeilenprodukt der Join-Tabelle die summierten Ergebnisse multipliziert, was zu ungenauen Ergebnissen führt.

Angenommen, es gibt ein Beispiel, bei dem zunächst zwei Abfragen verwendet werden, um SUM-Werte aus verschiedenen Tabellen abzurufen. Abfrage 1 berechnet die Gesamtfahrzeit für einen bestimmten Mitarbeiter während eines bestimmten Datumsbereichs, während Abfrage 2 die Gesamtarbeitszeit für denselben Mitarbeiter im selben Zeitraum berechnet.

Der Versuch, diese Abfragen in einer einzigen Join-Abfrage zu kombinieren, führt zu falschen Summen. Dies liegt daran, dass die JOIN-Operation das kartesische Produkt der Tabellenzeilen bhds_timecard und bhds_mileage erstellt. Dadurch werden die SUM-Werte für Lenkzeit und Arbeitszeit mit der Anzahl übereinstimmender Zeilen in jeder Tabelle multipliziert.

Um dieses Problem zu lösen, können Sie eine Unterabfrage verwenden, um den SUM-Wert zu berechnen, bevor Sie die Tabellen zusammenführen. Indem Sie die SUM-Operation in eine separate Unterabfrage verschieben, können Sie das kartesische Produkt verhindern und genaue Ergebnisse erhalten.

Das Folgende ist eine Abfrage, die mithilfe von Unterabfragen verbessert wurde:

SELECT last_name, first_name, DATE_FORMAT(LEAST(mil_date, tm_date), '%m/%d/%y') AS dates, 
       total, minutes
FROM bhds_teachers AS i
LEFT JOIN (
    SELECT ds_id, YEARWEEK(mil_date) AS week, MIN(mil_date) AS mil_date, SUM(drive_time) AS minutes
    FROM bhds_mileage
    WHERE mil_date BETWEEN '2016-04-11' AND '2016-04-30'
    AND bhds_mileage.ds_id = 5
    GROUP BY ds_id, week
) AS m ON m.ds_id = i.ds_id
LEFT JOIN (
    SELECT ds_id, YEARWEEK(tm_date) AS week, MIN(tm_date) AS tm_date, SUM(tm_hours) AS total
    FROM bhds_timecard
    WHERE tm_date BETWEEN '2016-04-11' AND '2016-04-30' AND bhds_timecard.ds_id = 5
    GROUP BY ds_id, week
) AS t ON t.ds_id = i.ds_id AND t.week = m.week;

In dieser Abfrage:

• Unterabfrage m Berechnet die Gesamtfahrzeit gruppiert nach Mitarbeiter-ID und Woche.
• Unterabfrage t Berechnet die insgesamt geleisteten Arbeitsstunden, gruppiert nach Mitarbeiter-ID und Woche.
• Die Hauptabfrage verknüpft die Tabelle bhds_teachers mit den Unterabfragen m und t.

Als Ergebnis ruft die Abfrage den Nachnamen, den Vornamen, das Datum, die Gesamtarbeitszeit und die Gesamtfahrzeit für den Mitarbeiter und den angegebenen Zeitraum ab, ohne dass das Problem der Multiplikation von SUM-Werten auftritt.

Das obige ist der detaillierte Inhalt vonWie vermeide ich multiplizierte SUM-Werte beim Verknüpfen von Tabellen mit Aggregatfunktionen in MySQL?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!