sql - Club Update query with group by 基于两个属性//postgres

Question

如何根据两个属性的 group by 导出的计数更新表？

例如:这是一个包含 a,b,c 列的表

我需要根据具有 common(a,b) 的计数更新 c

Answer 1

PostgreSQL 中的更新有一个 FROM extension你可以使用:

update  YourTable as yt1
set     c = case when aggr.cnt > 5 then 'Q' else 'Z' end
from    (
        select  a
        ,       b
        ,       count(*) as cnt
        from    YourTable
        group by
                a
        ,       b
        ) as aggr
where   aggr.a = yt1.a
        and aggr.b = yt1.b