sql - 在 PostgreSQL 中加入三个表后计算行数

Question

假设我在 PostgreSQL 中有三个表:

table1 - id1, a_id, updated_by_id
table2 - id2, a_id, updated_by_id
Users  - id, display_name

假设我正在使用以下查询:

select count(t1.id1) from table1 t1 
left join table2 t2 on (t1.a_id=t2.a_id) 
full outer join users u1 t1.updated_by_id=u1.id)
full outer join users u2 t2.updated_by_id=u2.id) 
where u1.id=100;

我的计数是 50。

鉴于:

select count(t1.id1) from table1 t1 
left join table2 t2 on (t1.a_id=t2.a_id) 
full outer join users u1 t1.updated_by_id=u1.id)
full outer join users u2 t2.updated_by_id=u2.id) 
where u2.id=100;

我只得到 25 作为计数。

我在第二个查询中的错误是什么？我该怎么做才能获得相同的计数？

我的要求是有一个用户表，被多个表引用。我想获取完整的用户列表并从不同的表中获取 ID 的数量。

但我单独加入的表返回正确的计数，但其余表不返回正确的计数。有人可以建议一种方法来修改我的第二个查询以获得正确的计数吗？

Answer 1

为了简化您的逻辑，先聚合，后加入。

猜测缺失的详细信息，此查询将为您提供准确的计数，即每个用户分别在 table1 和 table2 中被引用的次数 所有用户:

SELECT *
FROM   users u
LEFT   JOIN (
   SELECT updated_by_id AS id, count(*) AS t1_ct
   FROM   table1
   GROUP  BY 1
   ) t1 USING (id)
LEFT   JOIN (
   SELECT updated_by_id AS id, count(*) AS t2_ct
   FROM   table2
   GROUP  BY 1
   ) t2 USING (id);

特别是，避免多个 1-n 关系在连接在一起时相互相乘:

• Two SQL LEFT JOINS produce incorrect result

要仅检索单个或几个用户，LATERAL 连接会更快(Postgres 9.3+):

SELECT *
FROM   users u
LEFT   JOIN  LATERAL (
   SELECT count(*) AS t1_ct
   FROM   table1
   WHERE  updated_by_id = u.id
   ) ON true
LEFT   JOIN  LATERAL (
   SELECT count(*) AS t2_ct
   FROM   table2
   WHERE  updated_by_id = u.id
   ) ON true
WHERE  u.id = 100;

• What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

解释感知差异

您报告的特定不匹配是由于 FULL OUTER JOIN 的特殊性造成的:

First, an inner join is performed. Then, for each row in T1 that doesnot satisfy the join condition with any row in T2, a joined row isadded with null values in columns of T2. Also, for each row of T2 thatdoes not satisfy the join condition with any row in T1, a joined rowwith null values in the columns of T1 is added.

因此，您会在另一侧附加 NULL 值以表示缺少匹配项。 count() 不计算 NULL 值。因此，根据过滤 u1.id=100 还是 u2.id=100，您可以获得不同的结果。

这只是为了说明，这里不需要FULL JOIN。请改用提供的替代方案。