如何实现SQL分组后同一ID仅首行保留sum值其余行置0？

当前执行的SQL语句如下：

select
   cpri.sequential,
   cpri.id,
   coalesce(sum(pdr.something), 0) something,
   ...
from
   cpri
inner join cpr on cpr.id = cpri.id
left join pdr on pdr.other_id = cpr.other_id
where
   ...
group by
   cpri.sequential,
   cpri.id,
   ...
order by
   cpri.id desc

当前输出 #

sequential|id |something|other_information
      2637|880| 15000.00|              ...
      2635|880| 15000.00|              ...
      2636|880| 15000.00|              ...
      2638|880| 15000.00|              ...
      2624|876|  6000.00|              ...
      2625|876|  6000.00|              ...
      2611|870|  2000.00|              ...
      2612|870|  2000.00|              ...
      2613|870|  2000.00|              ...
      2614|870|  2000.00|              ...
      2571|858|  5000.00|              ...
      2572|858|  5000.00|              ...
      2569|858|  5000.00|              ...
      2570|858|  5000.00|              ...
       133| 68|  6366.90|              ...
       134| 68|  6366.90|              ...
       130| 66|   120.00|              ...
       129| 66|   120.00|              ...

期望输出 #

sequential|id |something|other_information
      2637|880| 15000.00|              ...
      2635|880|     0.00|              ...
      2636|880|     0.00|              ...
      2638|880|     0.00|              ...
      2624|876|  6000.00|              ...
      2625|876|     0.00|              ...
      2611|870|  2000.00|              ...
      2612|870|     0.00|              ...
      2613|870|     0.00|              ...
      2614|870|     0.00|              ...
      2571|858|  5000.00|              ...
      2572|858|     0.00|              ...
      2569|858|     0.00|              ...
      2570|858|     0.00|              ...
       133| 68|  6366.90|              ...
       134| 68|     0.00|              ...
       130| 66|   120.00|              ...
       129| 66|     0.00|              ...

解决方案 #

可以通过窗口函数实现需求，具体步骤：

1. 提前计算每个id对应的总聚合值，避免重复关联求和；
2. 用ROW_NUMBER()标记每个id分组内的行顺序（按sequential降序，匹配示例首行规则）；
3. 通过条件判断，仅分组首行显示聚合值，其余行置0。

修改后的SQL语句如下：

WITH aggregated_data AS (
    -- 预计算每个id的总something值
    SELECT
        cpr.id,
        COALESCE(SUM(pdr.something), 0) AS total_something
    FROM
        cpr
        LEFT JOIN pdr ON pdr.other_id = cpr.other_id
    WHERE
        -- 保留原查询WHERE条件
        ...
    GROUP BY
        cpr.id
),
ranked_data AS (
    -- 为每个id分组的行标记序号
    SELECT
        cpri.sequential,
        cpri.id,
        ad.total_something,
        cpri.other_information, -- 替换为实际需要的其他字段
        ROW_NUMBER() OVER (PARTITION BY cpri.id ORDER BY cpri.sequential DESC) AS rn
    FROM
        cpri
        INNER JOIN cpr ON cpr.id = cpri.id
        INNER JOIN aggregated_data ad ON ad.id = cpri.id
    WHERE
        -- 保留原查询WHERE条件
        ...
)
SELECT
    sequential,
    id,
    CASE WHEN rn = 1 THEN total_something ELSE 0.00 END AS something,
    other_information
FROM
    ranked_data
ORDER BY
    id DESC,
    sequential DESC;

说明 #

• aggregated_data CTE提前计算聚合值，减少主查询的重复计算，提升效率；
• ROW_NUMBER()的PARTITION BY cpri.id确保每个id分组独立编号，ORDER BY cpri.sequential DESC保证首行是该分组内sequential最大的行，和示例输出一致；
• 主流SQL方言（PostgreSQL、MySQL 8.0+、SQL Server等）均支持窗口函数，若使用旧版本MySQL（<8.0），可通过变量模拟行号逻辑，写法会稍复杂。

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