SQL查询：同PAYCODE优先取LOOKUP=COSTID行否则取空值行

查询gl_table_db表需满足以下规则：

• 返回结果中PAYCODE字段值唯一
• 单PAYCODE下的行返回优先级：
  1. 第一优先级：存在LOOKUP字段值等于COSTID字段值的行时，优先返回该行
  2. 第二优先级：若不存在上述匹配行，返回该PAYCODE下LOOKUP、COSTID均为null的行

源表数据 #

• PAYCODE=201，LOOKUP=null，COSTID=null，ACCOUNT=720001
• PAYCODE=201，LOOKUP=659057，COSTID=659057，ACCOUNT=999999
• PAYCODE=202，LOOKUP=null，COSTID=null，ACCOUNT=720002
• PAYCODE=202，LOOKUP=659058，COSTID=659057，ACCOUNT=999999

期望输出 #

• PAYCODE=201，LOOKUP=659057，COSTID=659057，ACCOUNT=999999
• PAYCODE=202，LOOKUP=null，COSTID=null，ACCOUNT=720002

原有写法存在两个核心错误：

1. 逻辑判断维度缺失：判断是否存在LOOKUP=COSTID的行时，没有按PAYCODE分组关联，会出现跨PAYCODE的错误判断
2. 子句逻辑错误：NOT EXISTS子句的匹配条件写为t1.LOOKUP = t2.LOOKUP，和需求要求的「判断同PAYCODE下是否存在LOOKUP等于COSTID的行」逻辑完全不符，加上SQL中AND优先级高于OR，会导致条件组合不符合预期。

写法1：窗口函数（逻辑清晰，推荐） #

用窗口函数按PAYCODE分组，给每行按规则标记优先级排序，取每组排名第一的行即可：

SELECT 
    PAYCODE,
    LOOKUP,
    COSTID,
    ACCOUNT
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY PAYCODE 
            ORDER BY 
                CASE WHEN LOOKUP = COSTID THEN 1
                     WHEN LOOKUP IS NULL AND COSTID IS NULL THEN 2
                     ELSE 3 END
        ) AS rn
    FROM gl_table_db
) t
WHERE rn = 1

写法2：NOT EXISTS写法（兼容不支持窗口函数的旧版数据库） #

SELECT t1.*
FROM gl_table_db t1
WHERE 
    (t1.LOOKUP = t1.COSTID)
    OR 
    (
        t1.LOOKUP IS NULL 
        AND t1.COSTID IS NULL
        AND NOT EXISTS (
            SELECT 1 
            FROM gl_table_db t2 
            WHERE t2.PAYCODE = t1.PAYCODE AND t2.LOOKUP = t2.COSTID
        )
    )

说明：上述写法默认每个PAYCODE下最多1条LOOKUP=COSTID的行、最多1条双null行，如果同优先级存在多条重复行，可根据业务规则补充排序条件，或把ROW_NUMBER()替换为RANK()避免数据遗漏。

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