如何在单条SQL中实现按去重ID求和（Analytical Function SUM with Distinct）

解决方案 #

完全可以在单条SQL中实现该需求，无需拆分成分步查询，核心逻辑是先按ID对源数据去重，再基于去重后的结果执行求和运算即可。

你原来的查询没有做去重处理，直接求和会把重复ID的金额也计入，最终得到的总和是23，不符合预期。以下是两种可行的实现写法：

写法1：直接统计去重后的总金额

WITH data AS
(
    SELECT 1 AS ID ,'ABC' AS Name, 'Paid' AS Status, 10 AS Amount
    UNION ALL
    SELECT 1 AS ID ,'ABC' AS Name, 'Paid' AS Status, 10 AS Amount
    UNION ALL
    SELECT 2 AS ID ,'ABC' AS Name, 'Paid' AS Status, 1 AS Amount
    UNION ALL
    SELECT 3 AS ID ,'ABC' AS Name, 'Paid' AS Status, 1 AS Amount
    UNION ALL
    SELECT 4 AS ID ,'ABC' AS Name, 'Paid' AS Status, 1 AS Amount
)
SELECT SUM(Amount) AS total_amount
FROM (
    -- 内层查询先完成ID去重
    SELECT DISTINCT ID, Amount, Name, Status
    FROM data
) t

运行后得到的total_amount结果为13，符合预期。

写法2：保留窗口函数逻辑，先去重再开窗

如果你需要保留每行明细+分组求和的结果格式，可以嵌套一层CTE做去重：

WITH data AS
(
    SELECT 1 AS ID ,'ABC' AS Name, 'Paid' AS Status, 10 AS Amount
    UNION ALL
    SELECT 1 AS ID ,'ABC' AS Name, 'Paid' AS Status, 10 AS Amount
    UNION ALL
    SELECT 2 AS ID ,'ABC' AS Name, 'Paid' AS Status, 1 AS Amount
    UNION ALL
    SELECT 3 AS ID ,'ABC' AS Name, 'Paid' AS Status, 1 AS Amount
    UNION ALL
    SELECT 4 AS ID ,'ABC' AS Name, 'Paid' AS Status, 1 AS Amount
),
deduplicated_data AS (
    -- 按ID去重，同一个ID只保留1条记录
    SELECT DISTINCT ID, Name, Status, Amount FROM data
)
SELECT *, SUM(Amount) OVER (PARTITION BY Name, Status) AS total_amount
FROM deduplicated_data

扩展说明

如果存在同一个ID对应的其他字段（Name/Status/Amount）不一致的场景，可以改用ROW_NUMBER()函数自定义去重规则，比如每个ID只取第一条记录：

deduplicated_data AS (
    SELECT * FROM (
        SELECT *,
        ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS rn
        FROM data
    ) t WHERE rn = 1
)

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