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

我有四张桌子：A、B、C、D。
表B有a的外键
表C有一个外键
表D有C的外键

到目前为止我能问的是：
我知道如何从A中选择所有行并计算B的连接行数。
我知道如何从A中选择所有行并从D（通过C）中计算连接行的数量。
现在我想将这两个查询合并到一个select中，并显示count和count，但是结果select显示错误的count。我是这样做的：

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存档？

最佳答案

由于要连接3个表，因此应在计数内使用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"