MySQL GROUP BY WITH ROLLUP如何将汇总行fee列设为空值

我有两张通过fee_name关联的表bmctypes和bmc_fee，使用GROUP BY WITH ROLLUP进行分组统计计算时，汇总行的fee列显示了上一行的17.70（无实际意义），预期该列为空字符串。尝试了IF函数、GROUPING函数、COALESCE+CASE等多种方法仍未成功，求适配表结构的正确SQL写法。

表结构 #

bmc_fee（共3行） #

|fee_name | fee   | 
|Adult    | 23.90 | 
|Student  | 19.75 | 
|U18      | 17.70 | 

bmctypes（约600行） #

|fee_name |
|Adult    |
|Adult    |
|Adult    |
|Student  |
|Adult    |
|U18      |
|Student  |
...

初始SQL及结果 #

初始SQL #

SELECT
   IF(GROUPING(bmctypes.fee_name), 'xTotal', bmctypes.fee_name) AS fee_name,
   COUNT(*) AS num,
   bmc_fee.fee, 
   bmc_fee.fee * COUNT(*)  AS expense
FROM  bmctypes 
JOIN bmc_fee 
ON bmc_fee.fee_name  = bmctypes.fee_name
GROUP BY bmctypes.fee_name WITH ROLLUP;

初始结果（汇总行fee列异常） #

|fee_name | num | fee   | expense  |
|Adult    | 560 | 23.90 | 13384.00 |
|Student  |   3 | 19.75 |    59.25 |
|U18      |  10 | 17.70 |   177.00 |
|xTotal   | 573 | 17.70 | 10142.10 |

预期结果 #

|fee_name | num | fee   | expense  |
|Adult    | 560 | 23.90 | 13384.00 |
|Student  |   3 | 19.75 |    59.25 |
|U18      |  10 | 17.70 |   177.00 |
|xTotal   | 573 |       | 10142.10 |

尝试过的方法及结果 #

尝试2：IF函数添加调试列 #

SELECT
   IF(GROUPING(bmctypes.fee_name), 'xTotal', bmctypes.fee_name) AS fee_name,
   COUNT(*) AS num,
   bmc_fee.fee as fee_1, 
   IF(fee_name ='xTotal','',bmc_fee.fee) AS fee_2,
   bmc_fee.fee * COUNT(*)  AS expense
FROM  bmctypes 
JOIN bmc_fee 
ON bmc_fee.fee_name  = bmctypes.fee_name
GROUP BY bmctypes.fee_name WITH ROLLUP;

结果

|fee_name | num | fee_1 | fee_2 | expense  |
|Adult    | 560 | 23.90 | (NULL)| 13384.00 |
|Student  |   3 | 19.75 | (NULL |    59.25 |
|U18      |  10 | 17.70 | (NULL |   177.00 |
|xTotal   | 573 | 17.70 | (NULL)| 10142.10 |

尝试3：GROUPING函数添加调试列 #

SELECT
   IF(GROUPING(bmctypes.fee_name), 'xTotal', bmctypes.fee_name) AS fee_name,
   COUNT(*) AS num,
   fee as fee_1,  
   IF(GROUPING(fee_name), '', bmc_fee.fee) AS fee_2,
   bmc_fee.fee * COUNT(*)  AS expense
FROM  bmctypes 
JOIN bmc_fee 
ON bmc_fee.fee_name  = bmctypes.fee_name
GROUP BY bmctypes.fee_name WITH ROLLUP;

结果

|fee_name | num | fee_1 | fee_2 | expense  |
|Adult    | 560 | 23.90 | (NULL)| 13384.00 |
|Student  |   3 | 19.75 | (NULL |    59.25 |
|U18      |  10 | 17.70 | (NULL |   177.00 |
|xTotal   | 573 | 17.70 |       | 10142.10 |

尝试4：COALESCE+CASE #

SELECT
   COALESCE(bmctypes.fee_name,'xTotal') AS fee_name,
   COUNT(*) AS num,
   fee as fee_1,  
  (CASE WHEN bmctypes.fee_name IS NULL THEN NULL ELSE fee END) AS fee_col_2,
   bmc_fee.fee * COUNT(*)  AS expense
FROM  bmctypes 
JOIN bmc_fee 
ON bmc_fee.fee_name  = bmctypes.fee_name
GROUP BY bmctypes.fee_name WITH ROLLUP;

结果

|fee_name | num | fee_1 | fee_col_2 | expense  |
|Adult    | 560 | 23.90 | (NULL)| 13384.00 |
|Student  |   3 | 19.75 | (NULL |    59.25 |
|U18      |  10 | 17.70 | (NULL |   177.00 |
|xTotal   | 573 | 17.70 | (NULL)| 10142.10 |

正确解决方案 #

问题根源在于直接关联两张表后使用ROLLUP，非聚合列bmc_fee.fee会被取分组最后一行的值。正确做法是先统计bmctypes的分组数量，再关联bmc_fee，同时用GROUPING函数判断汇总行并设置fee列为空：

SELECT
    IF(GROUPING(t.fee_name), 'xTotal', t.fee_name) AS fee_name,
    t.num,
    CASE WHEN GROUPING(t.fee_name) THEN '' ELSE f.fee END AS fee,
    SUM(IF(f.fee IS NOT NULL, f.fee * t.num, 0)) AS expense
FROM (
    -- 先统计每个fee_name的数量及汇总
    SELECT fee_name, COUNT(*) AS num
    FROM bmctypes
    GROUP BY fee_name WITH ROLLUP
) t
-- 左连接避免汇总行关联到无效数据
LEFT JOIN bmc_fee f ON t.fee_name = f.fee_name
ORDER BY GROUPING(t.fee_name), t.fee_name;

说明 #

1. 子查询先完成bmctypes的分组统计和ROLLUP汇总，确保分组逻辑独立。
2. 左连接bmc_fee，汇总行的fee_name为NULL，不会关联到任何fee值。
3. 通过CASE WHEN GROUPING(t.fee_name) THEN '' ELSE f.fee END控制汇总行的fee列为空字符串。
4. SUM函数计算总费用，确保汇总行的expense是各分组的费用总和。

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