Wie können mehrere Left-Joins zu falschen Zählergebnissen in SQL führen und wie kann dies behoben werden?

Fallen und Lösungen für SQL-Multiple-Left-Joins, die zu falschen Zählergebnissen führen

Wenn bei der Verwendung von SQL mehrere Linksverknüpfungen nicht korrekt verarbeitet werden, kann es zu unerwarteten Ergebnissen kommen. Betrachten Sie die folgende Abfrage:

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

Diese Abfrage versucht, die Anzahl der Besuche in zwei verwandten Tabellen users und grocery für jeden Benutzer in der Tabelle fishmarket zu zählen. Aufgrund der Natur von Left-Joins führt dies jedoch zu falschen Ergebnissen.

Missverständnisse über Linksverknüpfungen

In SQL werden Verknüpfungen von links nach rechts durchgeführt. Bei dieser Abfrage wird zunächst ein Left-Join zwischen users und grocery durchgeführt. Dies führt dazu, dass jeder Benutzerdatensatz mit dem entsprechenden Lebensmitteleinkaufsdatensatz verknüpft wird. Als nächstes wird ein zweiter Left-Join zwischen dem ersten Join-Ergebnis und fishmarket durchgeführt, was bedeutet, dass jeder Benutzerdatensatz mit einem Lebensmitteleinkaufsdatensatz weiter mit dem entsprechenden Fischmarkt-Einkaufsdatensatz verknüpft wird.

Multiplikation statt Addition

Eine unbeabsichtigte Folge dieser sequentiellen Verarbeitung ist, dass die Zugriffszahlen von grocery und fishmarket multipliziert und nicht addiert werden. Wenn ein Benutzer beispielsweise drei Lebensmittelgeschäfte und vier Fischmarktbesuche hatte, würde die Abfrage 12 statt der erwarteten 7 Besuche ergeben.

Lösung: Unterabfragen zur Aggregation verwenden

Um dies zu korrigieren, müssen wir sicherstellen, dass wir eine Aggregation (Zählung) der Anzahl der Besuche durchführen, bevor wir die Tabellen zusammenführen. Dies kann durch die Verwendung einer Unterabfrage erreicht werden:

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

Diese modifizierte Abfrage aggregiert die Besuche in einer Unterabfrage und verknüpft dann die aggregierten Ergebnisse mit der users-Tabelle, um sicherzustellen, dass die Besuche nicht versehentlich multipliziert werden.

Das obige ist der detaillierte Inhalt vonWie können mehrere Left-Joins zu falschen Zählergebnissen in SQL führen und wie kann dies behoben werden?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!