如何在PostgreSQL中用CTE和子查询实现特定保单过滤规则？

我们的目标是先筛选出所有存在status='cancel'的保单（no_polis）的全部记录，再从中排除掉那些最后一条生产记录（tgl_produksi最新的那条）状态也是cancel的保单。用CTE可以清晰拆分逻辑，一步步实现：

步骤1：锁定存在cancel记录的保单范围 #

先通过CTE提取所有包含cancel状态的保单编号：

WITH has_cancel_polis AS (
    SELECT DISTINCT no_polis
    FROM origin_polis
    WHERE status = 'cancel'
),

步骤2：获取每个保单的最后一条生产记录状态 #

用窗口函数ROW_NUMBER()按保单分组，按生产时间倒序排序，取每组第一条（最新记录）并记录其状态：

last_production_status AS (
    SELECT 
        no_polis,
        status AS last_status
    FROM (
        SELECT 
            no_polis,
            status,
            ROW_NUMBER() OVER (PARTITION BY no_polis ORDER BY tgl_produksi DESC) AS rn
        FROM origin_polis
    ) sub_query
    WHERE rn = 1
)

步骤3：关联筛选得到最终结果 #

将原始表与两个CTE关联，过滤出最后一条记录状态不是cancel的保单的全部数据：

SELECT op.*
FROM origin_polis op
JOIN has_cancel_polis hcp ON op.no_polis = hcp.no_polis
JOIN last_production_status lps ON op.no_polis = lps.no_polis
WHERE lps.last_status != 'cancel';

简化写法（可选） #

如果不想拆分多个CTE，也可以把第二步逻辑直接嵌入子查询：

WITH has_cancel_polis AS (
    SELECT DISTINCT no_polis
    FROM origin_polis
    WHERE status = 'cancel'
)
SELECT op.*
FROM origin_polis op
JOIN has_cancel_polis hcp ON op.no_polis = hcp.no_polis
WHERE op.no_polis IN (
    SELECT no_polis
    FROM (
        SELECT 
            no_polis,
            status,
            ROW_NUMBER() OVER (PARTITION BY no_polis ORDER BY tgl_produksi DESC) AS rn
        FROM origin_polis
    ) sub_query
    WHERE rn = 1 AND status != 'cancel'
);

核心逻辑就是：先锁定有cancel记录的保单范围，再排除掉最新记录也是cancel的保单，剩下的就是符合要求的所有记录。

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