两个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 (或任何任意数字)，请使用 查看全文