Pourquoi mes résultats MySQL SUM() sont-ils incorrects lors de la jointure de plusieurs tables ?

MySQL SUM() et jointures multi-tables : éviter les agrégations incorrectes

La combinaison de SUM() calculs avec plusieurs jointures de tables dans MySQL nécessite un examen attentif pour éviter des résultats inexacts. Un écueil courant résulte de l'effet produit cartésien lors des jointures, conduisant à des sommes gonflées.

Le problème : les résultats SUM() gonflés

Un utilisateur a tenté de consolider deux requêtes, chacune calculant des sommes provenant de tables différentes, en une seule requête jointe.

Requête 1 (Kilométrage) : Additionne le temps de trajet (en minutes) par semaine, regroupé par enseignant.

<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>

Requête 2 (Carte de pointage) : Additionne le total des heures par semaine, regroupées par enseignant.

<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>

Échec de la tentative de connexion :

La tentative de l'utilisateur de combiner ces requêtes a directement abouti à des sommes incorrectes :

<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>

Le problème ? Les SUM() fonctions ont été appliquées après la jointure, conduisant à la multiplication des sommes en raison de la possibilité de plusieurs lignes correspondantes entre les tables jointes.

La solution : sous-requêtes pré-agrégées

La bonne approche consiste à pré-agréger les sommes dans des sous-requêtes avant de rejoindre :

<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>

En effectuant les SUM() opérations dans des sous-requêtes distinctes, nous garantissons que les données de chaque table sont correctement agrégées avant la jointure, empêchant la multiplication des sommes et produisant des résultats précis. Le teacher_id (ou équivalent) est crucial pour la bonne condition de jointure.

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!