如何在SQL中基于分区列值生成标识列？贷款场景求解

针对你的需求——给每笔贷款按发送信件的情况分配Flag（仅A=1，仅B=2，两者都发=3），以下是几种高效的实现方案：

方法一：窗口函数直接计算 #

利用窗口函数统计每笔贷款下的不同信件类型数量，再结合CASE逻辑分配Flag：

SELECT 
    LOAN_NO,
    Letter,
    CASE 
        -- 当该贷款只有一种信件类型时，按信件分配1或2
        WHEN COUNT(DISTINCT Letter) OVER (PARTITION BY LOAN_NO) = 1 THEN 
            CASE WHEN Letter = 'A' THEN 1 ELSE 2 END
        -- 存在两种信件类型时直接设为3
        ELSE 3
    END AS Flag
FROM TableA;

逻辑说明：COUNT(DISTINCT Letter) OVER (PARTITION BY LOAN_NO)会计算每笔贷款对应的唯一信件数量，以此判断是单一信件还是两种都有，再匹配对应的Flag值。

方法二：预统计贷款信件状态再关联 #

先通过分组统计每笔贷款是否存在A、B信件，再关联回原表分配Flag：

WITH LoanLetterStats AS (
    SELECT 
        LOAN_NO,
        -- 标记是否存在Letter A
        MAX(CASE WHEN Letter = 'A' THEN 1 ELSE 0 END) AS HasA,
        -- 标记是否存在Letter B
        MAX(CASE WHEN Letter = 'B' THEN 1 ELSE 0 END) AS HasB
    FROM TableA
    GROUP BY LOAN_NO
)
SELECT 
    t.LOAN_NO,
    t.Letter,
    CASE 
        WHEN HasA = 1 AND HasB = 0 THEN 1
        WHEN HasA = 0 AND HasB = 1 THEN 2
        ELSE 3
    END AS Flag
FROM TableA t
JOIN LoanLetterStats ls ON t.LOAN_NO = ls.LOAN_NO;

逻辑说明：先在CTE中统计每笔贷款的A、B信件存在状态，再通过关联把状态映射到原表的每一行，逻辑清晰，适合后续需要扩展更多信件类型的场景。

方法三：用EXISTS判断其他信件存在性 #

通过EXISTS子查询直接判断当前贷款是否存在另一种信件，进而分配Flag：

SELECT 
    LOAN_NO,
    Letter,
    CASE 
        -- 如果当前贷款存在其他类型的信件，直接设为3
        WHEN EXISTS (SELECT 1 FROM TableA t2 WHERE t2.LOAN_NO = t.LOAN_NO AND t2.Letter != t.Letter) THEN 3
        -- 只有当前信件时，按类型分配1或2
        WHEN Letter = 'A' THEN 1
        ELSE 2
    END AS Flag
FROM TableA t;

逻辑说明：EXISTS子查询会快速判断同贷款下是否有其他信件，无需额外统计，逻辑直观易懂。

关于你之前的行号方法 #

你尝试的ROW_NUMBER() OVER(PARTITION BY LOAN_NO, Letter ORDER BY LOAN_NO)其实是给每个(贷款+信件)组合生成行号，这和需求中“判断贷款的整体信件类型”不匹配，所以会绕远路。上面的三种方法都更直接贴合需求。

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