SQL Server：如何筛选最大ID记录并汇总消费与总计值？

DevOps 小满

2026-5-8

解决方案：多类别筛选最新记录并汇总求和

这问题很常见，我来帮你把单个类别的查询扩展成多类别汇总的版本～

首先直接给你能实现需求的完整SQL语句，两种写法供你选择：

写法一：子查询嵌套

SELECT SUM(s.consumption) AS consumption, SUM(s.total) AS total
FROM (
    SELECT 
        consumption, 
        total, 
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY id DESC) AS rn
    FROM [dbo].[Table_1]
    WHERE category IN ('A', 'B')
) AS s
WHERE s.rn = 1;

写法二：CTE（公共表表达式），可读性更强

WITH RankedCategories AS (
    SELECT 
        category,
        consumption, 
        total, 
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY id DESC) AS rn
    FROM [dbo].[Table_1]
    WHERE category IN ('A', 'B')
)
SELECT SUM(consumption) AS consumption, SUM(total) AS total
FROM RankedCategories
WHERE rn = 1;

逻辑拆解（完全匹配你的需求）：

锁定目标类别：把你原来单个类别的WHERE category = 'A'改成WHERE category IN ('A', 'B')，这样就能同时处理两个类别了。
给同类别记录排名：用ROW_NUMBER() OVER (PARTITION BY category ORDER BY id DESC)，每个类别内部按id降序排列，id最大的那条记录会被标记为rn = 1。
筛选最新记录并汇总：外层查询只保留rn = 1的记录（也就是每个类别里最新的那条），再用SUM()函数分别对consumption和total求和，就能得到你要的100 400结果。