Bagaimana untuk Menjumlahkan Nilai Agregat dengan Betul daripada Jadual Bergabung dalam MySQL untuk Mengelakkan Isu Silang Produk?

Menggunakan pengagregatan SUM untuk menyertai jadual dalam MySQL: menyelesaikan masalah merentas produk

Menggabungkan dua pertanyaan menggunakan fungsi agregat SUM selalunya memberikan cabaran dalam MySQL. Hasil silang antara jadual boleh menyebabkan nilai SUM yang salah. Untuk menyelesaikan masalah ini, fungsi SUM perlu dirangkumkan ke dalam subkueri.

Pertimbangkan contoh berikut di mana dua pertanyaan mendapatkan semula SUM(masa_pandu) dan SUM(tm_hours) masing-masing untuk tarikh tertentu dan ID guru sebanyak 5:

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

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

Percubaan sambungan mudah:

Untuk menggabungkan pertanyaan ini, cara mudah ialah menggabungkannya seperti berikut:

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

Walau bagaimanapun, pendekatan ini mencipta produk silang antara jadual, mengakibatkan nilai SUM yang salah.

Penyelesaian:

Untuk mendapatkan nilai SUM yang betul, alihkan fungsi agregat SUM ke dalam subkueri. Ini menghalang masalah silang produk dan memastikan bahawa nilai SUM hanya dikira dalam baris yang berkaitan:

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

Atas ialah kandungan terperinci Bagaimana untuk Menjumlahkan Nilai Agregat dengan Betul daripada Jadual Bergabung dalam MySQL untuk Mengelakkan Isu Silang Produk?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!