求助：PostgreSQL按邮件服务器分组更新指定记录Valid状态

原语句的问题在于：

• 会将所有记录数≥6的EmailServer分组下的全部记录的Valid设为true，没有筛选每组的前3条
• 子查询中的LIMIT 5会限制仅处理5个符合条件的EmailServer，这与需求不符

以下是满足需求的SQL语句：

WITH ranked_contacts AS (
    SELECT 
        "Email",
        "EmailServer",
        -- 按EmailServer分组给每条记录编号，排序字段可根据实际需求调整（比如创建时间）
        ROW_NUMBER() OVER (PARTITION BY "EmailServer" ORDER BY "Email") AS row_num,
        -- 计算每组的总记录数
        COUNT(*) OVER (PARTITION BY "EmailServer") AS total_in_group
    FROM public."EmailContacts"
)
UPDATE public."EmailContacts" ec
SET "Valid" = CASE
    -- 组内记录数≥6且是前3条时设为true，其余设为false
    WHEN rc.total_in_group >= 6 AND rc.row_num <= 3 THEN true
    ELSE false
END
FROM ranked_contacts rc
-- 通过唯一字段关联原表和CTE
WHERE ec."Email" = rc."Email" AND ec."EmailServer" = rc."EmailServer";

关键说明： #

• 使用ROW_NUMBER()窗口函数为每个EmailServer分组内的记录生成序号，ORDER BY后的字段可根据业务需求替换（比如按记录创建时间排序）
• 用COUNT(*) OVER (PARTITION BY "EmailServer")获取每个分组的总记录数，判断是否满足≥6的条件
• 通过CASE语句精准控制Valid字段的值，确保只有符合条件的前3条记录被设为true

内容的提问来源于stack exchange，提问作者canbrian