MySQL行累计总和（Running total）计算：基于现有表的查询问询

咱们先聊聊你当前的查询语句问题：你定义了@runtot:=0这个变量，但后面的SELECT语句完全没用到它，而且当前语句只是把agent维度转成了列（也就是数据透视），并没有实现累计总和的逻辑，所以肯定达不到你想要的效果。

下面分两种MySQL版本场景给你优化方案，你可以根据自己的版本选择：

一、MySQL 8.0及以上（推荐，支持窗口函数） #

这是最简洁高效的方案，利用SUM() OVER()窗口函数直接计算累计值，不需要手动维护变量：

场景1：每个Agent每天的累计值（按Agent分组累计） #

如果需要分别统计每个Agent从起始日期到当天的累计hold值，同时保留每日的单个Agent数值，可以这么写：

-- 先计算每个Agent每天的数值和累计值
WITH daily_agent_data AS (
    SELECT 
        date,
        agent,
        SUM(hold) AS daily_hold,
        -- 按Agent分组，按日期排序，累计求和
        SUM(SUM(hold)) OVER (PARTITION BY agent ORDER BY date) AS running_total
    FROM your_table_name -- 替换成你的实际表名
    GROUP BY date, agent
)
-- 转成列展示
SELECT 
    date,
    MAX(CASE WHEN agent = 'A' THEN daily_hold ELSE 0 END) AS A_daily,
    MAX(CASE WHEN agent = 'A' THEN running_total ELSE 0 END) AS A_running,
    MAX(CASE WHEN agent = 'B' THEN daily_hold ELSE 0 END) AS B_daily,
    MAX(CASE WHEN agent = 'B' THEN running_total ELSE 0 END) AS B_running,
    MAX(CASE WHEN agent = 'C' THEN daily_hold ELSE 0 END) AS C_daily,
    MAX(CASE WHEN agent = 'C' THEN running_total ELSE 0 END) AS C_running,
    MAX(CASE WHEN agent = 'D' THEN daily_hold ELSE 0 END) AS D_daily,
    MAX(CASE WHEN agent = 'D' THEN running_total ELSE 0 END) AS D_running
FROM daily_agent_data
GROUP BY date
ORDER BY date;

场景2：所有Agent的总累计值（每日总累计） #

如果只需要统计从起始日期到当天所有Agent的总hold累计值，写法更简单：

SELECT 
    date,
    SUM(CASE WHEN agent = 'A' THEN hold ELSE 0 END) AS A,
    SUM(CASE WHEN agent = 'B' THEN hold ELSE 0 END) AS B,
    SUM(CASE WHEN agent = 'C' THEN hold ELSE 0 END) AS C,
    SUM(CASE WHEN agent = 'D' THEN hold ELSE 0 END) AS D,
    -- 按日期排序，累计所有hold的总和
    SUM(SUM(hold)) OVER (ORDER BY date) AS total_running_total
FROM your_table_name -- 替换成你的实际表名
GROUP BY date
ORDER BY date;

二、MySQL 5.7及以下（不支持窗口函数） #

如果你的MySQL版本较低，只能用用户变量来维护累计值，需要注意变量的初始化和排序逻辑：

-- 初始化每个Agent的累计变量
SET @runtot_a = 0, @runtot_b = 0, @runtot_c = 0, @runtot_d = 0;

SELECT 
    date,
    MAX(CASE WHEN agent = 'A' THEN daily_hold ELSE 0 END) AS A,
    MAX(CASE WHEN agent = 'A' THEN running_total ELSE 0 END) AS A_running,
    MAX(CASE WHEN agent = 'B' THEN daily_hold ELSE 0 END) AS B,
    MAX(CASE WHEN agent = 'B' THEN running_total ELSE 0 END) AS B_running,
    MAX(CASE WHEN agent = 'C' THEN daily_hold ELSE 0 END) AS C,
    MAX(CASE WHEN agent = 'C' THEN running_total ELSE 0 END) AS C_running,
    MAX(CASE WHEN agent = 'D' THEN daily_hold ELSE 0 END) AS D,
    MAX(CASE WHEN agent = 'D' THEN running_total ELSE 0 END) AS D_running
FROM (
    SELECT 
        date,
        agent,
        SUM(hold) AS daily_hold,
        -- 根据Agent类型更新对应的累计变量
        CASE 
            WHEN agent = 'A' THEN @runtot_a := @runtot_a + SUM(hold)
            WHEN agent = 'B' THEN @runtot_b := @runtot_b + SUM(hold)
            WHEN agent = 'C' THEN @runtot_c := @runtot_c + SUM(hold)
            WHEN agent = 'D' THEN @runtot_d := @runtot_d + SUM(hold)
        END AS running_total
    FROM your_table_name -- 替换成你的实际表名
    GROUP BY date, agent
    -- 必须按日期和Agent排序，保证累计逻辑正确
    ORDER BY date, agent
) AS temp_data
GROUP BY date
ORDER BY date;

额外说明 #

• 如果你的Agent列表是动态变化的（不确定有多少个），静态的CASE WHEN写法会比较繁琐，这时候可以考虑用动态SQL来自动生成列，但需要在应用层或者存储过程中实现。
• 不管用哪种方案，date字段最好保证是日期类型，并且建立索引，这样排序和分组的性能会更好。

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