SQL中基于另一列唯一值聚合列的实现（含约束）

给定如下数据表：

已知规则：同一ID在指定Date下的Cost固定，仅Color字段存在差异。需求是按ID对跨Date的Cost求和，同时在结果集中保留Color字段。

常规求和查询（如下）能得到正确结果，但无法包含Color：

SELECT inner.ID, SUM(inner.Cost) 
FROM (SELECT ID, Cost FROM YourTable GROUP BY ID, Cost) as inner 
GROUP BY ID;

若在内层查询加入Color并按ID, Color分组，会导致ID=1的SUM(Cost)计算为30（Green和Black对应的5、10分别被重复计入），不符合要求。

约束条件：

• 无法通过关联查询单独获取Color字段
• 无法在内层查询使用DISTINCT
• 无法修改数据结构
• 无法拆分查询

可以利用窗口函数+行号过滤的方式实现，既满足求和正确性，又保留Color字段：

SELECT
    ID,
    SUM(Cost) AS Total_Cost,
    Color
FROM (
    SELECT
        ID,
        Cost,
        Color,
        -- 按ID+Date分组，给每组标记行号
        ROW_NUMBER() OVER (PARTITION BY ID, Date ORDER BY Color) AS row_num
    FROM YourTable
) AS sub
-- 只保留每组的第一行，确保同一ID+Date的Cost仅被计算一次
WHERE row_num = 1
GROUP BY ID, Color;

结果说明 #

执行上述查询后，会得到如下结果：

该结果中，每个ID+Color组合对应的Total_Cost都是跨Date的正确求和值，同时保留了所有Color信息，符合需求。

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