如何计算postgres中的多个连接行？-Java 学习之路

我有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 进行存档？

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"

回复于 2024-04-29T15:10:14+08:00

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

1 回答

相关问题