MySQL同OFFICECODE下VAL累计求和问题求助

原始数据 #

OFFICECODE | DATE       | VAL
10200      | 2018-05-01 | 1
10200      | 2018-05-02 | 2
10100      | 2018-05-01 | 3
10100      | 2018-05-02 | 2

期望结果 #

OFFICECODE | DATE       | SUM(VAL)
10200      | 2018-05-01 | 1
10200      | 2018-05-02 | 3 (1+2)
10100      | 2018-05-01 | 3
10100      | 2018-05-02 | 5 (3+2)

你尝试的错误查询 #

SELECT A.OFFICELKD, A.DATE, A.VAL, @VAL := @VAL + A.VAL AS `VALSUM`
FROM (
    SELECT cur.OFFICELKD, cur.DATE, cur.VAL
    FROM tblReport cur
    LEFT JOIN tblReport pre ON cur.OFFICECODE = pre.OFFICECODE
    GROUP BY cur.ID
    ORDER BY OFFICECODE
) AS A, ( SELECT @VAL := 0 ) AS SqlA
ORDER BY OFFICECODE, DATE

问题分析与修正方案 #

你当前的查询用了全局变量@VAL，但没处理OFFICECODE切换的场景——当OFFICECODE从10200变成10100时，@VAL没有重置，导致会把前一个OFFICECODE的累计值加到新组里，所以才出现了“对所有OFFICECODE求和”的问题。

我们需要额外跟踪当前的OFFICECODE，遇到新的OFFICECODE时重置累计值，同一组内则继续累加。以下是修正后的查询：

SELECT 
    A.OFFICECODE, 
    A.DATE, 
    A.VAL,
    -- 判断当前OFFICECODE是否和上一行相同，相同则累加，不同则重置为当前VAL
    @VAL := CASE 
        WHEN @PREV_OFFICE = A.OFFICECODE THEN @VAL + A.VAL 
        ELSE A.VAL 
    END AS `VALSUM`,
    -- 更新上一个OFFICECODE为当前值，供下一行判断使用
    @PREV_OFFICE := A.OFFICECODE AS `_`
FROM (
    -- 先按OFFICECODE和DATE排序，保证每个组内的累计顺序正确
    SELECT OFFICECODE, DATE, VAL
    FROM tblReport
    ORDER BY OFFICECODE, DATE
) AS A, 
-- 初始化两个变量：累计值@VAL、上一个OFFICECODE@PREV_OFFICE
( SELECT @VAL := 0, @PREV_OFFICE := '' ) AS SqlVars
ORDER BY A.OFFICECODE, A.DATE

如果你想得到和期望结果完全一致的、带计算过程的格式，可以在结果里拼接字符串：

SELECT 
    A.OFFICECODE, 
    A.DATE,
    CONCAT(
        @VAL := CASE 
            WHEN @PREV_OFFICE = A.OFFICECODE THEN @VAL + A.VAL 
            ELSE A.VAL 
        END,
        -- 仅在组内非第一行时，显示计算过程
        CASE 
            WHEN @PREV_OFFICE = A.OFFICECODE THEN CONCAT(' (', @VAL - A.VAL, '+', A.VAL, ')') 
            ELSE '' 
        END
    ) AS `SUM(VAL)`,
    @PREV_OFFICE := A.OFFICECODE AS `_`
FROM (
    SELECT OFFICECODE, DATE, VAL
    FROM tblReport
    ORDER BY OFFICECODE, DATE
) AS A, 
( SELECT @VAL := 0, @PREV_OFFICE := '' ) AS SqlVars
ORDER BY A.OFFICECODE, A.DATE

这样就能输出你想要的带计算说明的累计结果啦！

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