Postgres 12中含JOIN与CASE的UPDATE语句实现求助

需求说明 #

更新post_comment_response表的approval_status字段，遵循以下规则：

• 若所有有效审批人已完成审批且无人拒绝，状态设为approved；
• 若无关联的team_member_manager记录（即无有效审批人），状态设为approved；
• 若有任何有效审批人拒绝，状态设为rejected；
• 其他情况（有有效审批人但未全部完成审批，且无人拒绝）状态设为pending。

注：仅team_member_manager表中指定的审批人有效，post_comment_response_approval中的其他记录不计入统计。

表结构与测试数据 #

post_comment_response表

post_comment表

post表

team_member_manager表

team_member表

post_comment_response_approval表

修正后的UPDATE语句 #

WITH pcr_approval_summary AS (
    SELECT
        pcr.id,
        -- 统计有效审批人中拒绝的数量
        SUM(CASE WHEN pcra.approved = false THEN 1 ELSE 0 END) AS reject_count,
        -- 统计有效审批人中已提交审批的数量（无论通过/拒绝）
        SUM(CASE WHEN pcra.approved IS NOT NULL THEN 1 ELSE 0 END) AS completed_count,
        -- 有效审批人的总数量
        COUNT(DISTINCT tmm.managing_team_member_id) AS total_approvers
    FROM post_comment_response pcr
    JOIN post_comment pc ON pc.id = pcr.post_comment_id
    JOIN post p ON p.id = pc.post_id
    -- 左连接获取有效审批人（team_id=91的管理者）
    LEFT JOIN team_member_manager tmm 
        ON tmm.managed_team_member_id = p.team_member_id
    LEFT JOIN team_member mgr 
        ON mgr.id = tmm.managing_team_member_id 
        AND mgr.team_id = 91
    -- 左连接有效审批人的审批记录
    LEFT JOIN post_comment_response_approval pcra 
        ON pcra.post_comment_response_id = pcr.id 
        AND pcra.team_member_id = tmm.managing_team_member_id
    GROUP BY pcr.id
)
UPDATE post_comment_response pcr
SET approval_status = 
    CASE
        -- 优先判断：存在有效审批人拒绝的情况
        WHEN summary.reject_count > 0 THEN 'rejected'
        -- 无有效审批人，或所有有效审批人都已完成审批且无人拒绝
        WHEN summary.total_approvers = 0 OR summary.completed_count = summary.total_approvers THEN 'approved'
        -- 剩余情况：有审批人但未全部完成，且无人拒绝
        ELSE 'pending'
    END
FROM pcr_approval_summary summary
WHERE pcr.id = summary.id;

逻辑解释 #

1. CTE聚合统计：通过pcr_approval_summary先计算每个post_comment_response的核心指标：
   • reject_count：有效审批人中标记为拒绝的数量
   • completed_count：有效审批人中已提交审批（approved不为NULL）的数量
   • total_approvers：关联到的有效审批人总数
2. CASE条件匹配：
   • 先判断是否有拒绝记录，若有直接设为rejected（符合需求3）
   • 再判断是否无有效审批人，或所有审批人都已完成审批且无拒绝，设为approved（符合需求1、2）
   • 其余情况设为pending（符合需求4）

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