Wie summiere ich aggregierte Werte aus verknüpften Tabellen in MySQL korrekt, um produktübergreifende Probleme zu vermeiden?

Verwendung der SUM-Aggregation zum Verbinden von Tabellen in MySQL: Lösung produktübergreifender Probleme

Das Kombinieren zweier Abfragen mithilfe der SUM-Aggregatfunktion stellt in MySQL oft eine Herausforderung dar. Kreuzprodukte zwischen Tabellen können zu falschen SUM-Werten führen. Um dieses Problem zu lösen, muss die SUM-Funktion in eine Unterabfrage gekapselt werden.

Betrachten Sie das folgende Beispiel, in dem zwei Abfragen SUM(drive_time) bzw. SUM(tm_hours) für ein bestimmtes Datum und eine Lehrer-ID von 5 abrufen:

Abfrage 1:

SELECT last_name, first_name, DATE_FORMAT(mil_date, '%m/%d/%y') AS dates, 
SUM(drive_time) MINUTES 
FROM bhds_mileage 
LEFT JOIN bhds_teachers i 
ON i.ds_id = bhds_mileage.ds_id 
WHERE mil_date BETWEEN '2016-04-11' AND '2016-04-30' 
AND bhds_mileage.ds_id = 5 
GROUP BY CONCAT(YEAR(mil_date), '/', WEEK(mil_date)), bhds_mileage.ds_id 
ORDER BY last_name ASC, dates ASC

Abfrage 2:

SELECT last_name, first_name, DATE_FORMAT(tm_date, '%m/%d/%y') AS dates,   
SUM(tm_hours) total 
FROM bhds_timecard 
LEFT JOIN bhds_teachers i 
ON i.ds_id = bhds_timecard.ds_id 
WHERE tm_date BETWEEN '2016-04-11' AND '2016-04-30' AND bhds_timecard.ds_id = 5 
GROUP BY CONCAT(YEAR(tm_date), '/', WEEK(tm_date)), bhds_timecard.ds_id 
ORDER BY last_name ASC, dates ASC

Einfacher Verbindungsversuch:

Um diese Abfragen zu kombinieren, besteht eine einfache Möglichkeit darin, sie wie folgt zu verketten:

SELECT last_name, first_name, DATE_FORMAT(tm_date, '%m/%d/%y') AS dates,  
SUM(tm_hours) total, SUM(drive_time) MINUTES 
FROM bhds_timecard 
LEFT JOIN bhds_teachers i 
ON i.ds_id = bhds_timecard.ds_id 
LEFT JOIN bhds_mileage 
ON DATE_FORMAT(bhds_timecard.tm_date, '%m/%d/%y') = DATE_FORMAT(bhds_mileage.mil_date, '%m/%d/%y') AND bhds_timecard.ds_id = bhds_mileage.ds_id 
WHERE tm_date BETWEEN '2016-04-11' AND '2016-04-30' AND bhds_timecard.ds_id = 5 
GROUP BY CONCAT(YEAR(tm_date), '/', WEEK(tm_date)), bhds_timecard.ds_id 

Dieser Ansatz führt jedoch zu Kreuzprodukten zwischen Tabellen, was zu falschen SUM-Werten führt.

Lösung:

Um korrekte SUM-Werte zu erhalten, verschieben Sie die SUM-Aggregatfunktion in eine Unterabfrage. Dies verhindert Kreuzproduktprobleme und stellt sicher, dass SUM-Werte nur in relevanten Zeilen berechnet werden:

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 

Das obige ist der detaillierte Inhalt vonWie summiere ich aggregierte Werte aus verknüpften Tabellen in MySQL korrekt, um produktübergreifende Probleme zu vermeiden?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!