Bagaimanakah Gabungan Berbilang Kiri Boleh Membawa kepada Keputusan Kiraan yang Tidak Betul dalam SQL, dan Bagaimana Ini Boleh Dibetulkan?

Perangkap dan penyelesaian untuk SQL berbilang gabungan kiri yang membawa kepada hasil pengiraan yang salah

Apabila menggunakan SQL, jika gabungan berbilang kiri tidak dikendalikan dengan betul, hasil yang tidak dijangka mungkin berlaku. Pertimbangkan pertanyaan berikut:

<code class="language-sql">SELECT
    t1."id" AS "User ID",
    t1.account_balance AS "Account Balance",
    count(t2.user_id) AS "# of grocery visits",
    count(t3.user_id) AS "# of fishmarket visits"
FROM users t1
LEFT OUTER JOIN grocery t2 ON (t2.user_id=t1."id") 
LEFT OUTER JOIN fishmarket t3 ON (t3.user_id=t1."id") 
GROUP BY t1.account_balance,t1.id
ORDER BY t1.id</code>

Pertanyaan ini cuba mengira bilangan lawatan ke dua jadual yang berkaitan users dan grocery untuk setiap pengguna dalam jadual fishmarket. Walau bagaimanapun, disebabkan sifat cantuman kiri, ia menghasilkan hasil yang salah.

Salah faham tentang penyertaan kiri

Dalam SQL, cantuman dilakukan dari kiri ke kanan. Dalam pertanyaan ini, sambung kiri antara users dan grocery mula-mula dilakukan. Ini menyebabkan setiap rekod pengguna disambungkan ke rekod pembelian runcit yang sepadan. Seterusnya, sambungan kiri kedua dilakukan antara hasil penyertaan pertama dan fishmarket, yang bermaksud setiap rekod pengguna dengan rekod pembelian runcit akan disambungkan lagi dengan rekod pembelian pasaran ikan yang sepadan.

Pendaraban bukan penambahan

Akibat yang tidak diingini daripada pemprosesan berjujukan ini ialah pengiraan akses daripada grocery dan fishmarket didarab dan bukannya ditambah. Sebagai contoh, jika pengguna mempunyai 3 lawatan runcit dan 4 lawatan pasar ikan, pertanyaan itu akan menghasilkan 12 lawatan berbanding 7 yang dijangkakan.

Penyelesaian: Gunakan subkueri untuk pengagregatan

Untuk membetulkannya, kami perlu memastikan kami melakukan pengagregatan (kiraan) bilangan lawatan sebelum menyertai jadual. Ini boleh dicapai dengan menggunakan subkueri:

<code class="language-sql">SELECT u.id
, u.account_balance
, g.grocery_visits
, f.fishmarket_visits
FROM   users u
LEFT   JOIN (
   SELECT user_id, count(*) AS grocery_visits
   FROM   grocery
   GROUP  BY user_id
   ) g ON g.user_id = u.id
LEFT   JOIN (
   SELECT user_id, count(*) AS fishmarket_visits
   FROM   fishmarket
   GROUP  BY user_id
   ) f ON f.user_id = u.id
ORDER  BY u.id;</code>

Pertanyaan yang diubah suai ini mengagregatkan lawatan dalam subkueri dan kemudian menggabungkan hasil agregat dengan jadual users, memastikan lawatan tidak didarab secara tidak sengaja.

Atas ialah kandungan terperinci Bagaimanakah Gabungan Berbilang Kiri Boleh Membawa kepada Keputusan Kiraan yang Tidak Betul dalam SQL, dan Bagaimana Ini Boleh Dibetulkan?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!