如何用单条PostgreSQL查询生成含overall_status的服务器结果表

完全可以用单条SELECT语句直接生成包含com_passfail、os_passfail和overall_status的结果，无需分两次创建视图。核心思路是将生成前两个状态列的CASE表达式嵌入主查询，再基于这些计算列直接推导overall_status。

示例查询语句 #

假设你用于生成com_passfail和os_passfail的规则如下（可根据实际业务逻辑调整CASE里的判断条件）：

SELECT
    "compliance status",
    "operating system version",
    "reference ID" AS ref_id,
    -- 生成合规状态的PASS/FAIL列
    CASE
        WHEN "compliance status" IN ('PASS', '已合规') THEN 'PASS'
        ELSE 'FAIL'
    END AS com_passfail,
    -- 生成操作系统版本的PASS/FAIL列
    CASE
        WHEN "operating system version" LIKE '%最新%' OR "operating system version" = 'PASS' THEN 'PASS'
        ELSE 'FAIL'
    END AS os_passfail,
    -- 生成整体状态
    CASE
        -- 仅当存在FAIL状态且ref_id为空时，整体状态为FAIL
        WHEN (
            (CASE WHEN "compliance status" IN ('PASS', '已合规') THEN 'PASS' ELSE 'FAIL' END = 'FAIL'
            OR CASE WHEN "operating system version" LIKE '%最新%' OR "operating system version" = 'PASS' THEN 'PASS' ELSE 'FAIL' END = 'FAIL')
            AND "reference ID" IS NULL
        ) THEN 'FAIL'
        -- 其余所有情况均为PASS
        ELSE 'PASS'
    END AS overall_status
FROM servers;

简化写法（提升可读性） #

如果觉得重复写CASE表达式冗余，可以用子查询先计算前两个状态列，再推导整体状态：

SELECT
    s.*,
    CASE
        WHEN (com_passfail = 'FAIL' OR os_passfail = 'FAIL') AND ref_id IS NULL THEN 'FAIL'
        ELSE 'PASS'
    END AS overall_status
FROM (
    SELECT
        "compliance status",
        "operating system version",
        "reference ID" AS ref_id,
        CASE
            WHEN "compliance status" IN ('PASS', '已合规') THEN 'PASS'
            ELSE 'FAIL'
        END AS com_passfail,
        CASE
            WHEN "operating system version" LIKE '%最新%' OR "operating system version" = 'PASS' THEN 'PASS'
            ELSE 'FAIL'
        END AS os_passfail
    FROM servers
) s;

逻辑说明 #

整体状态的判断逻辑可简化为：

• 唯一触发FAIL的场景：合规状态或操作系统版本至少有一个为FAIL，且ref_id为空；
• 所有其他场景（包括有任意一个PASS、有FAIL但ref_id非空）均返回PASS，完全匹配你给出的三条规则。

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