单表场景下如何用CTE筛选流转异常的银行卡生产数据？

你原来的CTE存在语法错误：重复定义了同名的failure表，且CTE之间未用逗号分隔，导致无法正常执行。下面是针对需求的正确解决方案：

要找出经历过printing阶段后被退回molding阶段的卡片，核心是匹配同一张卡片的两类记录：

• 存在printing阶段且结果非Success的记录（说明印刷出问题）
• 该卡片在上述印刷失败的记录之后，存在Molding阶段且结果为Success的记录（说明被退回重成型）

假设你的表中有记录阶段发生时间的字段（比如stage_time，如果没有则用自增日志ID替代），以下是两种可行方案：

方案一：自连接查询 #

直接将表与自身连接，关联同卡片的两类记录并校验时间顺序：

select distinct s1.barcode
from [Audit]..Stages s1 (nolock)
join [Audit]..Stages s2 (nolock)
    on s1.barcode = s2.barcode
    -- 确保molding是在printing失败之后发生的，排除初始的molding阶段
    and s2.stage_time > s1.stage_time
where s1.clientID = 'AmericanExpress'
    and s1.stagename = 'printing'
    and s1.Description <> 'Success'
    and s2.clientID = 'AmericanExpress'
    and s2.stagename = 'Molding'
    and s2.Description = 'Success'

方案二：CTE关联查询 #

先分别筛选出两类记录，再按卡片关联并校验顺序，逻辑更清晰：

;with printing_fail as (
    select barcode, stage_time
    from [Audit]..Stages (nolock)
    where clientID = 'AmericanExpress'
        and stagename = 'printing'
        and Description <> 'Success'
),
molding_success_after as (
    select barcode, stage_time
    from [Audit]..Stages (nolock)
    where clientID = 'AmericanExpress'
        and stagename = 'Molding'
        and Description = 'Success'
)
select distinct pf.barcode
from printing_fail pf
join molding_success_after msa
    on pf.barcode = msa.barcode
    and msa.stage_time > pf.stage_time

关键说明 #

• 必须依赖时间/日志ID字段判断阶段先后，否则无法区分是初始的molding阶段还是退回后的molding阶段。
• 如果同一张卡片不会重复出现符合条件的记录组合，可以去掉distinct提升查询效率。

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