如何编写SQL对指定loan_type外的重复记录汇总loan_amt

原始表数据 #

表名：TABLE_A

目标结果 #

需要得到如下查询结果：

需求说明 #

• 当user_name和loan_type组合相同时，汇总对应的loan_amt；
• 例外规则：loan_type为'5678'和'8765'的记录，即使user_name和loan_type重复，也不进行汇总，保留原始单条记录。

尝试过的SQL及问题 #

尝试1：全量分组汇总 #

SELECT 
    user_name, loan_type, 
    SUM(loan_amt) AS loan_amt 
FROM 
    TABLE_A 
GROUP BY 
    user_name, loan_type;

问题：会对所有user_name+loan_type组合进行汇总，包括'5678'和'8765'类型，不符合例外规则。

尝试2：错误的CASE语句 #

SELECT 
    user_name, loan_type, 
    CASE 
        WHEN loan_type = '5678' SUM(loan_amt) AS loan_amt
        WHEN loan_type = '8756' SUM(loan_amt) AS loan_amt 
        ELSE loan_type
    END AS loan_amt
FROM
    TABLE_A 
GROUP BY 
    user_name, loan_type;

问题：语法错误，逻辑混乱，无法正常执行。

解决方案 #

方案1：拆分处理+UNION ALL #

将数据分为两类处理：非例外类型正常汇总，例外类型直接输出原始记录，最后合并结果：

-- 处理非例外类型：按user_name和loan_type汇总金额
SELECT 
    user_name, 
    loan_type, 
    SUM(loan_amt) AS loan_amt
FROM TABLE_A
WHERE loan_type NOT IN ('5678', '8765')
GROUP BY user_name, loan_type

UNION ALL

-- 保留例外类型的所有原始记录
SELECT 
    user_name, 
    loan_type, 
    loan_amt
FROM TABLE_A
WHERE loan_type IN ('5678', '8765')

-- 可选：按user_name排序，匹配目标结果顺序
ORDER BY user_name, loan_type;

方案2：分组时加入唯一标识 #

通过给例外记录添加唯一分组键（item_key），让每个例外记录成为独立分组，从而保留原始金额：

SELECT 
    user_name, 
    loan_type, 
    SUM(loan_amt) AS loan_amt
FROM TABLE_A
GROUP BY 
    user_name, 
    loan_type,
    -- 例外类型按item_key单独分组，避免合并
    CASE WHEN loan_type IN ('5678', '8765') THEN item_key ELSE NULL END
ORDER BY user_name, loan_type;

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