Comment additionner correctement les valeurs agrégées des tables jointes dans MySQL pour éviter les problèmes entre produits ?

Utiliser l'agrégation SUM pour joindre des tables dans MySQL : résoudre des problèmes multi-produits

La combinaison de deux requêtes à l'aide de la fonction d'agrégation SUM présente souvent des défis dans MySQL. Les produits croisés entre les tables peuvent entraîner des valeurs SUM incorrectes. Pour résoudre ce problème, la fonction SUM doit être encapsulée dans une sous-requête.

Considérons l'exemple suivant où deux requêtes récupèrent respectivement SUM(drive_time) et SUM(tm_hours) pour une date spécifique et un identifiant d'enseignant de 5 :

Requête 1 :

<code class="language-sql">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</code>

Requête 2 :

<code class="language-sql">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</code>

Tentative de connexion simple :

Pour combiner ces requêtes, un moyen simple est de les concaténer comme suit :

<code class="language-sql">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 </code>

Cependant, cette approche crée des produits croisés entre les tables, ce qui entraîne des valeurs SUM incorrectes.

Solution :

Pour obtenir les valeurs SUM correctes, déplacez la fonction d'agrégation SUM dans une sous-requête. Cela évite les problèmes multi-produits et garantit que les valeurs SUM ne sont calculées que dans les lignes pertinentes :

<code class="language-sql">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 </code>

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!