MySQL如何按指定列分组计算累计值(Running Total)

方案1：MySQL 8.0及以上版本（推荐，窗口函数实现） #

直接用分区求和窗口函数，语法简洁不易出错：

SELECT 
  company,
  year,
  profit,
  CAST(SUM(profit) OVER (PARTITION BY company ORDER BY year) AS DECIMAL(16,2)) AS cum_profit
FROM `table`
GROUP BY company, year
ORDER BY company, year;

核心逻辑：PARTITION BY company会把数据按公司拆分成分区，每个分区内独立按年份升序累加利润，切换公司时自动重置累计值，完全符合需求。

方案2：MySQL 5.x及更低版本（用户变量兼容实现） #

你的原有写法只做了全局累计，没有新增公司切换的判断逻辑，修改后代码如下：

SELECT 
  company,
  year,
  profit,
  cum_profit
FROM (
  SELECT 
    company,
    year,
    profit,
    CAST(
      @cum_profit := IF(@prev_company = company, @cum_profit + profit, profit)
    AS DECIMAL(16,2)) AS cum_profit,
    @prev_company := company
  FROM (
    -- 必须先按公司、年份排序，保证变量计算的顺序正确
    SELECT company, year, profit
    FROM `table`
    GROUP BY company, year
    ORDER BY company, year
  ) AS sorted_data
  CROSS JOIN (SELECT @cum_profit := 0, @prev_company := '') AS init_var
) AS result;

注意事项：

• 变量计算依赖数据的排序顺序，必须先按company、year排序后再做累计计算
• 外层嵌套SELECT可以去掉辅助变量的输出字段，只保留需要的结果

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