多個左連接如何導致 SQL 中的計數結果不正確，以及如何修復此問題？

SQL 多重左連結導致計數結果錯誤的陷阱及解決方案

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

此查詢試圖計算 users 表中每個使用者的兩個相關表 grocery 和 fishmarket 的造訪次數。但是，由於左連接的特性，它會產生不正確的結果。

左連接的誤區

在 SQL 中，連線是從左到右執行的。在此查詢中，首先執行 users 和 grocery 之間的左連接。這導致每個用戶記錄與其對應的雜貨購買記錄連接。接下來，執行第一個連接結果與 fishmarket 之間的第二個左連接，這意味著每個帶有雜貨購買記錄的用戶記錄將進一步與其對應的魚市場購買記錄連接。

乘法而非加法

這種順序處理的意外結果是，來自 grocery 和 fishmarket 的訪問計數被相乘而不是相加。例如，如果一個用戶有 3 次雜貨訪問和 4 次魚市場訪問，則查詢將產生 12 次訪問，而不是預期的 7 次。

解決方案：使用子查詢進行聚合

為了修正這個問題，我們需要確保在連接表之前執行存取次數的聚合（計數）。這可以透過使用子查詢來實現：

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;

此修改後的查詢在子查詢中聚合訪問次數，然後將聚合的結果與 users 表連接，確保訪問次數不會被意外地相乘。

以上是多個左連接如何導致 SQL 中的計數結果不正確，以及如何修復此問題？的詳細內容。更多資訊請關注PHP中文網其他相關文章！