两个 SQL LEFT JOINS 产生不正确的结果 [英] Two SQL LEFT JOINS produce incorrect result

问题描述

我有 3 张桌子:

users(id, account_balance)
grocery(user_id, date, amount_paid)
fishmarket(user_id, date, amount_paid)

fishmarket 和 grocery 表都可能多次出现相同的 user_id，但对于任何给定的用户，不同的日期和支付的金额或根本没有.当我尝试以下查询时:

Both fishmarket and grocery tables may have multiple occurrences for the same user_id with different dates and amounts paid or have nothing at all for any given user. When I try the following query:

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

它产生了错误的结果:"1", "12", "12".
但是当我尝试 LEFT JOIN 到一个表时，它会为 grocery 或 fishmarket 访问产生正确的结果，它们是 "1"、3"、4".

It produces an incorrect results: "1", "12", "12".
But when I try to LEFT JOIN to just one table it produces a correct results for either grocery or fishmarket visits, which are "1", "3", "4".

我在这里做错了什么?
我使用的是 PostgreSQL 9.1.

What am I doing wrong here?
I am using PostgreSQL 9.1.

推荐答案

从左到右处理联接(除非括号另有规定).如果你LEFT JOIN(或只是JOIN，类似的效果)给一个用户三件杂货，你会得到3行(1 x 3).如果您随后为同一用户加入 4 个鱼市，您将获得 12 (3 x 4) 行，乘以结果中的前一个计数，而不是相加 到它，就像你所希望的那样.
从而使杂货店和鱼市的访问量成倍增加.

Joins are processed left to right (unless parentheses dictate otherwise). If you LEFT JOIN (or just JOIN, similar effect) three groceries to one user you get 3 rows (1 x 3). If you then join 4 fishmarkets for the same user, you get 12 (3 x 4) rows, multiplying the previous count in the result, not adding to it, like you may have hoped for.
Thereby multiplying the visits for groceries and fishmarkets alike.

你可以让它像这样工作:

You can make it work like this:

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;

要获取一个或几个用户的聚合值，相关子查询 如提供的@Vince很好.对于整个表或其中的主要部分，聚合 n 表并加入结果一次效率更高.这样，我们在外部查询中也不需要另一个 GROUP BY.

To get aggregated values for one or few users, correlated subqueries like @Vince provided are just fine. For a whole table or major parts of it, it is (much) more efficient to aggregate the n-tables and join to the result once. This way, we also do not need another GROUP BY in the outer query.

grocery_visits 和 fishmarket_visits 是 NULL 对于在相应表中没有任何相关条目的用户.如果您需要 0(或任意数字)，请使用 COALESCE 在外层 SELECT:

grocery_visits and fishmarket_visits are NULL for users without any related entries in the respective tables. If you need 0 instead (or any arbitrary number), use COALESCE in the outer SELECT:

SELECT u.id
     , u.account_balance
     , COALESCE(g.grocery_visits   , 0) AS grocery_visits
     , COALESCE(f.fishmarket_visits, 0) AS fishmarket_visits
FROM   ...

这篇关于两个 SQL LEFT JOINS 产生不正确的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！