两个SQL的LEFT JOIN操作产生了错误的结果

Question

我有3个表：

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

无论是fishmarket表还是grocery表，都可能出现同一用户的多个记录，这些记录具有不同的日期和付款金额，或者对于任何给定的用户根本没有任何记录。

当我尝试以下查询时：

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

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

- Ryan Bostwick

Answer 1

连接按从左到右的顺序处理（除非括号另有规定）。 如果您将三个杂货店与一个用户进行LEFT JOIN（或仅JOIN，效果类似），则会得到3行（1 x 3）。 然后，如果您为同一用户加入4个鱼市场，则会得到12个（3 x 4）行，结果中的前一个计数乘以后一个计数，而不是像您希望的那样添加。
因此，使杂货店和鱼市场的访问次数都增加了。

您可以这样做：

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。

grocery_visits和fishmarket_visits对于没有相关条目的用户是NULL的。如果您需要0（或任意数字），请在外部SELECT中使用COALESCE：

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

- Erwin Brandstetter

Answer 2

针对您最初的查询，如果去掉“group by”子句，查看未分组的结果，您会明白为什么会得到这些计数。

也许使用子查询的以下查询可以实现您的预期结果：

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

- Vince Perta

Answer 3

当用户表与杂货店表连接时，有3条记录匹配。然后这三条记录中的每一条都与鱼市场的4条记录匹配，产生12条记录。你需要使用子查询来获取你想要的结果。

- Tobsey

Answer 4

SELECT
     t1."id" AS "User ID",
     t1.account_balance AS "Account Balance",
     Sum(Case When t2.user_id is null then 0 else 1 end) AS "# of grocery visits",
     Sum(Case When t3.user_id is null then 0 else 1 end) 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

以上还将允许您根据需要添加额外的条件。

- Roger Google