PostgreSQL按条件将tmp表数据插入mo表并设置compliant_status

这个需求可以通过先统计每个HOST的状态分布，再关联原表生成对应compliant_status的方式实现，下面是具体的SQL语句和解释：

WITH host_status_summary AS (
    SELECT 
        HOST,
        BOOL_OR(STATUS = 'COMPLIANT') AS has_compliant,
        BOOL_OR(STATUS = 'NC') AS has_nc
    FROM tmp
    GROUP BY HOST
)
INSERT INTO mo (HOST, "UN NO.", STATUS, S_DATE, compliant_status)
SELECT 
    t.HOST,
    t."UN NO.",
    t.STATUS,
    t.S_DATE,
    CASE
        WHEN hss.has_compliant AND hss.has_nc THEN 'PARTIAL'
        WHEN hss.has_compliant THEN 'COMPLIANT'
        WHEN hss.has_nc THEN 'NON_COMPLIANT'
        ELSE NULL -- 处理STATUS为空等异常场景，可根据需求调整
    END AS compliant_status
FROM tmp t
JOIN host_status_summary hss ON t.HOST = hss.HOST;

逻辑拆解 #

1. HOST状态统计：

   • 用host_status_summary这个公共表表达式（CTE）按HOST分组，借助PostgreSQL的BOOL_OR聚合函数，快速判断每个HOST下是否存在COMPLIANT或NC状态的行——只要分组里有一行满足条件，函数就返回true，非常适合这种存在性判断场景。

2. 生成目标状态值：

   • 通过CASE分支语句，根据每个HOST的统计结果匹配对应的规则：
     • 同时存在COMPLIANT和NC → 设为PARTIAL
     • 仅存在COMPLIANT → 设为COMPLIANT
     • 仅存在NC → 设为NON_COMPLIANT

3. 插入目标表：

   • 将原tmp表的数据和统计结果关联，把需要的字段插入到mo表中。注意mo表的id是自增序列，不需要手动插入，数据库会自动生成对应值。

结果验证 #

执行上述SQL后，mo表的结果会完全符合你的预期：

• 所有RhelTest的行compliant_status为PARTIAL（该HOST同时存在两种状态）
• 所有Demo1的行compliant_status为NON_COMPLIANT（该HOST仅存在NC状态）
• Demo2的行compliant_status为COMPLIANT（该HOST仅存在COMPLIANT状态）

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