postgres基于关联联接表的计数更新值

Question

我有一个简单的场景TableA、TableB和JoinTable，它连接了TableA和TableB。我想在TableA中为TableA中的每一行存储JoinTable中具有TableAId的记录的计数。我可以正确地选择它如下：

SELECT "Id", (SELECT COUNT(*) FROM "JoinTable" WHERE "JoinTable"."TableAId" = "TableA"."Id") 
AS TOT FROM "TableA" LIMIT 100

但是，我很难编写更新查询。我想用这个结果更新TableA.JoinCount。

Answer 1

您可以使用group by查询作为UPDATE语句的源：

update "TableA" a
  set "JoinCount" = t.cnt
from (
  select "TableAId" as id, count(*) as cnt
  from "JoinTable" 
  group by "TableAId"
) t  
WHERE t.id = a."Id"

Answer 2

您可以使用相关子查询：

update tablea a
set tot = (
    select count(*)
    from jointable j
    where t.tableaid = a.id
)

这将使用来自jointable的匹配计数更新table a的所有行；如果没有匹配项，tot设置为0。

然而，我不一定建议存储这些派生信息。虽然上面的陈述很容易使它初始化，但维护它是乏味的。您很快就会发现自己正在为联接表上的每个DML操作（更新、删除、插入）创建触发器。相反，您可以将信息放在视图中：

create view viewa as
select id, 
    (select count(*) from jointable j where j.tableaid = a.id) as tot
from tablea a

旁注：一般来说，不要在Postgres中使用带引号的标识符。此链接可获取更多信息。