MySQL按年月统计唯一ref对应的total_price求和问题求助

问题分析 #

你的需求是每个ref的重复total_price仅计一次，再按年月维度汇总求和。原SQL中使用SUM(DISTINCT total_price)会错误地将同一分组内不同ref的相同total_price合并（比如2022-11的003和002的200会被当成同一个值只加一次），无法满足需求。

正确SQL写法 #

可以通过先按「年月+ref」维度去重，再按年月汇总的方式实现：

方法1：子查询分组去重

SELECT 
    CONCAT(YEAR(transDate), '-', LPAD(MONTH(transDate), 2, '0')) AS `Year/date`,
    SUM(unique_price) AS sum_of_total_price
FROM (
    -- 先按年月+ref分组，确保每个ref的total_price仅保留一条
    SELECT 
        YEAR(transDate) AS year,
        MONTH(transDate) AS month,
        ref,
        MAX(total_price) AS unique_price -- 同ref的total_price重复，取MAX/MIN/DISTINCT均可
    FROM your_table
    GROUP BY year, month, ref
) AS grouped_refs
GROUP BY year, month
ORDER BY `Year/date` ASC;

方法2：子查询DISTINCT去重

SELECT 
    CONCAT(YEAR(transDate), '-', LPAD(MONTH(transDate), 2, '0')) AS `Year/date`,
    SUM(total_price) AS sum_of_total_price
FROM (
    -- 直接去重年月+ref+total_price组合，确保每个ref在对应年月仅出现一次
    SELECT DISTINCT
        YEAR(transDate) AS year,
        MONTH(transDate) AS month,
        ref,
        total_price
    FROM your_table
) AS distinct_ref_prices
GROUP BY year, month
ORDER BY year, month ASC;

结果说明 #

两种写法都会输出符合要求的结果：

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