如何通过分组使CASE语句仅返回单条记录？求替代解决方案

当前查询返回两条记录的核心原因是关联了SCANS表中PAT_CAT_SEQ=500和600的两条数据，导致主表记录被拆分。要合并成单条记录，只需对两个CASE字段使用聚合函数保留存在的'T'值即可，具体修改如下：

修改后的SQL代码 #

SEL
    SPDM.PRODUCT
    ,SPDM.OCNTR
    ,SPDM.DCNTR
    ,SPDM.MLTYP
    ,SQ.RETURN_FLG
    ,SPDM.ODT
    ,SQ.F_PLT_F_PROC_WC ORIGINATING_WC
    ,SQ.F_PLT_F_PROC_DTM_LOC
    ,SCANS.RELATED_SCAN_WC DESTINATION_WC
    ,SCANS.RELATED_SCAN_DTM_LOC
    ,SPDM.ECUST CUSTOMER_ID
    ,SPDM.ITEM
    -- 使用MAX聚合，只要存在符合条件的记录就返回'T'
    ,MAX(CASE 
        WHEN SCANS.PAT_CAT_SEQ = 500
            AND CHECK_RESULT_CODE = 'OT'
        THEN 'T' ELSE 'F'
     END) LATE_PROCESS
    ,MAX(CASE 
        WHEN SCANS.PAT_CAT_SEQ = 600
            AND CHECK_RESULT_CODE = 'OT'
        THEN 'T' ELSE 'F'
     END) MISS_CUTOFF
FROM DL_SQ_PRD_INT.FCT_SPDM_FLASH SPDM
LEFT JOIN DL_SQ_PRD_INT.FCT_PAT_FLASH_SCAN SCANS
ON SPDM.ITEM = SCANS.PIN
    AND SPDM.FYFW = SCANS.FYFW
    AND SCANS.PAT_CAT_SEQ IN (500, 600)
    AND SCANS.DP0_IND = 1
INNER JOIN PIN_ORIGINATING_OT OOT
ON SPDM.ITEM = OOT.PIN
LEFT JOIN DL_SQ_PRD_INT.VW_FCT_ITEM SQ
ON SCANS.ITEM_ID = SQ.ITEM_ID
WHERE SPDM.ITEM = '2010778075710766'
-- 分组字段去掉原CASE对应的列，因为现在是聚合字段
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12;

原理说明 #

• 用MAX()函数包裹CASE语句：字符'T'的ASCII码大于'F'，因此同一主记录只要存在符合条件的SCANS记录，MAX会自动保留'T'值，否则返回'F'。
• 调整GROUP BY子句：移除原CASE对应的两个字段，避免因这两个字段的不同值导致分组拆分。

其他可选方案 #

方案1：使用PIVOT行转列（支持该语法的数据库适用） #

通过行转列将不同SEQ的结果转为同一行的两个字段：

WITH base_data AS (
    SEL
        SPDM.PRODUCT
        ,SPDM.OCNTR
        ,SPDM.DCNTR
        ,SPDM.MLTYP
        ,SQ.RETURN_FLG
        ,SPDM.ODT
        ,SQ.F_PLT_F_PROC_WC ORIGINATING_WC
        ,SQ.F_PLT_F_PROC_DTM_LOC
        ,SCANS.RELATED_SCAN_WC DESTINATION_WC
        ,SCANS.RELATED_SCAN_DTM_LOC
        ,SPDM.ECUST CUSTOMER_ID
        ,SPDM.ITEM
        ,CASE WHEN CHECK_RESULT_CODE = 'OT' THEN 'T' ELSE 'F' END AS FLAG_VALUE
        ,CASE SCANS.PAT_CAT_SEQ 
             WHEN 500 THEN 'LATE_PROCESS'
             WHEN 600 THEN 'MISS_CUTOFF'
         END AS FLAG_TYPE
    FROM DL_SQ_PRD_INT.FCT_SPDM_FLASH SPDM
    LEFT JOIN DL_SQ_PRD_INT.FCT_PAT_FLASH_SCAN SCANS
    ON SPDM.ITEM = SCANS.PIN
        AND SPDM.FYFW = SCANS.FYFW
        AND SCANS.PAT_CAT_SEQ IN (500, 600)
        AND SCANS.DP0_IND = 1
    INNER JOIN PIN_ORIGINATING_OT OOT
    ON SPDM.ITEM = OOT.PIN
    LEFT JOIN DL_SQ_PRD_INT.VW_FCT_ITEM SQ
    ON SCANS.ITEM_ID = SQ.ITEM_ID
    WHERE SPDM.ITEM = '2010778075710766'
)
SELECT *
FROM base_data
PIVOT (
    MAX(FLAG_VALUE)
    FOR FLAG_TYPE IN ('LATE_PROCESS' AS LATE_PROCESS, 'MISS_CUTOFF' AS MISS_CUTOFF)
) AS pvt;

方案2：两次LEFT JOIN关联SCANS表 #

分别关联不同SEQ的SCANS记录，从根源避免主记录被拆分：

SEL
    SPDM.PRODUCT
    ,SPDM.OCNTR
    ,SPDM.DCNTR
    ,SPDM.MLTYP
    ,SQ.RETURN_FLG
    ,SPDM.ODT
    ,SQ.F_PLT_F_PROC_WC ORIGINATING_WC
    ,SQ.F_PLT_F_PROC_DTM_LOC
    -- 用COALESCE合并两个SCANS的目标字段，可根据业务调整逻辑
    ,COALESCE(SCANS500.RELATED_SCAN_WC, SCANS600.RELATED_SCAN_WC) DESTINATION_WC
    ,COALESCE(SCANS500.RELATED_SCAN_DTM_LOC, SCANS600.RELATED_SCAN_DTM_LOC) RELATED_SCAN_DTM_LOC
    ,SPDM.ECUST CUSTOMER_ID
    ,SPDM.ITEM
    ,CASE WHEN SCANS500.CHECK_RESULT_CODE = 'OT' THEN 'T' ELSE 'F' END LATE_PROCESS
    ,CASE WHEN SCANS600.CHECK_RESULT_CODE = 'OT' THEN 'T' ELSE 'F' END MISS_CUTOFF
FROM DL_SQ_PRD_INT.FCT_SPDM_FLASH SPDM
INNER JOIN PIN_ORIGINATING_OT OOT
ON SPDM.ITEM = OOT.PIN
-- 单独关联PAT_CAT_SEQ=500的记录
LEFT JOIN DL_SQ_PRD_INT.FCT_PAT_FLASH_SCAN SCANS500
ON SPDM.ITEM = SCANS500.PIN
    AND SPDM.FYFW = SCANS500.FYFW
    AND SCANS500.PAT_CAT_SEQ = 500
    AND SCANS500.DP0_IND = 1
-- 单独关联PAT_CAT_SEQ=600的记录
LEFT JOIN DL_SQ_PRD_INT.FCT_PAT_FLASH_SCAN SCANS600
ON SPDM.ITEM = SCANS600.PIN
    AND SPDM.FYFW = SCANS600.FYFW
    AND SCANS600.PAT_CAT_SEQ = 600
    AND SCANS600.DP0_IND = 1
LEFT JOIN DL_SQ_PRD_INT.VW_FCT_ITEM SQ
-- 合并两个SCANS的ITEM_ID进行关联，需根据实际业务调整
ON COALESCE(SCANS500.ITEM_ID, SCANS600.ITEM_ID) = SQ.ITEM_ID
WHERE SPDM.ITEM = '2010778075710766';

注意：方案2需注意DESTINATION_WC等字段的取值逻辑，若两个SCANS记录的这些字段不同，要根据业务需求选择取其中一个或合并。

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