使用WITH ROLLUP时双聚合值运算列不生效的问题如何解决

问题根源 #

你之前的写法存在两个核心问题：

• t2的子查询是行级执行的，每次都会返回全表的聚合结果，放到SUM内部时会被重复计算多次，导致结果偏差
• ROLLUP只能对当前查询层级的聚合函数生效，你把差值计算逻辑放在聚合函数内层、关联了未参与分组的子查询，ROLLUP无法识别差值的聚合规则

解决方案 #

先预计算t2的总聚合值，再和t1的分组聚合结果做差，最后应用ROLLUP即可，代码示例如下（兼容MySQL 5.7+）：

SELECT 
    t1_group.name,
    t1_group.shortName,
    t1_group.t1_total - t2_total.t2_total AS variance
FROM (
    -- 先聚合t1每个分组的计算值，ROLLUP在这一层生成正确的小计、总计
    SELECT
        name,
        shortName,
        SUM(
            CASE 
                WHEN condition1 THEN qty
                WHEN condition2 THEN qty * multiplier 
            END
        ) AS t1_total
    FROM table t1
    GROUP BY name, shortName WITH ROLLUP
) t1_group
-- 预计算t2的总聚合值，全局只执行一次，避免行级重复查询
CROSS JOIN (
    SELECT
        SUM(
            CASE 
                WHEN condition1 THEN qty
                WHEN condition2 THEN qty * multiplier 
            END
        ) AS t2_total
    FROM table t2
) t2_total

如果你的t2也需要按name、shortName相同维度分组计算差值，只需要调整t2的子查询也按对应维度分组，再用LEFT JOIN关联t1和t2的分组结果即可：

SELECT 
    COALESCE(t1_group.name, t2_group.name) AS name,
    COALESCE(t1_group.shortName, t2_group.shortName) AS shortName,
    IFNULL(t1_group.t1_total, 0) - IFNULL(t2_group.t2_total, 0) AS variance
FROM (
    SELECT
        name,
        shortName,
        SUM(
            CASE 
                WHEN condition1 THEN qty
                WHEN condition2 THEN qty * multiplier 
            END
        ) AS t1_total
    FROM table t1
    GROUP BY name, shortName WITH ROLLUP
) t1_group
LEFT JOIN (
    SELECT
        name,
        shortName,
        SUM(
            CASE 
                WHEN condition1 THEN qty
                WHEN condition2 THEN qty * multiplier 
            END
        ) AS t2_total
    FROM table t2
    GROUP BY name, shortName WITH ROLLUP
) t2_group ON t1_group.name <=> t2_group.name AND t1_group.shortName <=> t2_group.shortName

方案说明 #

• 用子查询先单独完成两个表的分组聚合逻辑，ROLLUP会在各自的子查询内正确生成小计、总计行
• 外层仅做差值计算，不需要再做聚合，结果自然符合预期
• 用到的<=>是MySQL的安全等于运算符，可以正确匹配ROLLUP生成的NULL值，避免关联错误

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