postgresql - 如何计算 postgres 中的多个连接行？

Question

我有 4 个表:A、B、C、D。

• 表B有A的外键
• 表C有A的外键
• 表 D 有 C 的外键

到目前为止我可以查询的内容:

• 我知道如何从 A 中选择所有行并计算 B 中连接行的数量。
• 我知道如何选择 A 中的所有行并计算 D 中连接的行的数量(通过 C)。

现在我想将这两个查询合并到一个选择中，并在那里显示 count_b 和 count_d。但是生成的选择显示错误的计数。以下是我的做法:

SELECT
  "A"."id",
  COUNT("B"."id") AS "B_count",
  COUNT("D"."id") AS "D_count"
FROM "users" AS "A" LEFT OUTER JOIN "B" AS "B"
    ON "A"."id" = "B"."a__id"
  LEFT OUTER JOIN "C" AS "C"
    ON "A"."id" = "C"."a_id"
  LEFT OUTER JOIN "D" AS "D"
    ON "C"."id" = "D"."c_id"
GROUP BY "A"."id"

GROUP BY 可能存在问题。看来我需要为每个计数编写单独的分组依据。我正在寻找没有嵌套选择的结果(因为我仍然需要将其映射到 ORM，不要问为什么)。是否可以使用 distinct by 进行归档？

Answer 1

由于您要连接 3 个表，因此您应该在 COUNT 中使用 DISTINCT:

SELECT
  "A"."id",
  COUNT(DISTINCT "B"."id") AS "B_count",
  COUNT(DISTINCT "D"."id") AS "D_count"
FROM "users" AS "A" LEFT OUTER JOIN "B" AS "B"
    ON "A"."id" = "B"."a__id"
  LEFT OUTER JOIN "C" AS "C"
    ON "A"."id" = "C"."a_id"
  LEFT OUTER JOIN "D" AS "D"
    ON "C"."id" = "D"."c_id"
GROUP BY "A"."id"