postgresql 两个 SQL LEFT JOINS 产生不正确的结果

Two SQL LEFT JOINS produce incorrect result

提问by Ryan Bostwick

I have 3 tables:

我有3张桌子：

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

Both fishmarketand grocerytables 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:

双方fishmarket并grocery表可能有多次出现于不同日期同一USER_ID和支付数额或者什么都没有为任何给定用户。当我尝试以下查询时：

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

It produces an incorrect results: "1", "12", "12".
But when I try to LEFT JOINto just one table it produces a correct results for either groceryor fishmarketvisits, which are "1", "3", "4".

它产生不正确的结果："1", "12", "12".
但是，当我尝试LEFT JOIN仅使用一张表时，它会为grocery或fishmarket访问生成正确的结果，即"1", "3", "4".

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

我在这里做错了什么？
我正在使用 PostgreSQL 9.1。

回答by Erwin Brandstetter

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, multiplyingthe previous count in the result, not addingto it, like you may have hoped for.
Thereby multiplying the visits for groceries and fishmarkets alike.

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

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;

To get aggregated values for one or few users, correlated subquerieslike @Vince providedare 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 BYin the outer query.

为了获得一个或几个用户的聚合值，提供像@Vince 这样的相关子查询就可以了。对于整个表或其中的主要部分，聚合 n 表并连接到结果一次（效率更高）效率更高。这样，我们在外部查询中也不需要另一个。GROUP BY

grocery_visitsand fishmarket_visitsare NULLfor users without any related entries in the respective tables. If you need 0instead (or any arbitrary number), use COALESCE:

grocery_visits并且fishmarket_visits是NULL为用户没有相应的表中的任何相关条目。如果您需要0（或任何任意数字），请使用COALESCE：

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

回答by Vince Perta

For your original query, if you take away the group by to look at the pre-grouped result, you'll see why the counts your were receiving were created.

对于您的原始查询，如果您删除分组以查看预先分组的结果，您将了解为什么创建了您收到的计数。

Perhaps the following query utilizing subqueries would achieve your intended result:

也许使用子查询的以下查询会达到您的预期结果：

SELECT
 t1."id" AS "User ID",
 t1.account_balance AS "Account Balance",
 (SELECT count(*) FROM grocery     t2 ON (t2.user_id=t1."id")) AS "# of grocery visits",
 (SELECT count(*) FROM fishmarket  t3 ON (t3.user_id=t1."id")) AS "# of fishmarket visits"
FROM users t1
ORDER BY t1.id

回答by Tobsey

It's because when the user table joins to the grocery table, there are 3 records matched. Then each of those three records matches with the 4 records in fishmarket, producing 12 records. You need subqueries to get what you are looking for.

这是因为当用户表加入杂货表时，匹配了 3 条记录。然后这三个记录中的每一个都与fishmarket中的4个记录相匹配，产生12个记录。您需要子查询来获取您要查找的内容。