单条MySQL查询实现分组后求和需求

背景与问题

你原本通过以下查询获取了分组后的结果：

SELECT CT.*, CW.id_cw, CW.suivie_cw FROM ct AS CT 
JOIN sw AS SW ON CT.id_sw = SW.id_sw 
JOIN cw AS CW ON CT.id_sw = CW.id_cw
WHERE SW.`dateCrea_sw` 
BETWEEN UNIX_TIMESTAMP('2022-01-01 00:00:00') AND UNIX_TIMESTAMP('2022-12-31 23:59:59') 
AND CT.id_format IN (10, 11, 12) 
AND CT.quantite > 0
AND CW.suivie_cw = 1 GROUP BY CT.id_produit;

你需要对上述结果中的quantite列求和，但尝试嵌套子查询的方式时出现#1242 - Subquery returns more than 1 row错误，原因是子查询返回了多行分组后的quantite值，而SUM()无法直接接收多行结果。

解决方案：单条查询实现求和

不需要临时表，有两种直接的单查询写法：

方法1：将原查询作为派生表求和

把原分组查询作为一个派生表，在外层直接对quantite列求和：

SELECT SUM(t.quantite) AS total_quantite
FROM (
    SELECT CT.quantite FROM ct AS CT 
    JOIN sw AS SW ON CT.id_sw = SW.id_sw 
    JOIN cw AS CW ON CT.id_sw = CW.id_cw
    WHERE SW.`dateCrea_sw` 
    BETWEEN UNIX_TIMESTAMP('2022-01-01 00:00:00') AND UNIX_TIMESTAMP('2022-12-31 23:59:59') 
    AND CT.id_format IN (10, 11, 12) 
    AND CT.quantite > 0
    AND CW.suivie_cw = 1 
    GROUP BY CT.id_produit
) AS t;

注：原查询中的CT.*可简化为只选CT.quantite和CT.id_produit，减少不必要的数据传输。

方法2：利用WITH ROLLUP直接生成汇总

如果业务逻辑是先按id_produit分组，再对分组后的quantite求和，可使用WITH ROLLUP扩展，在结果末尾生成汇总行：

SELECT CT.id_produit, SUM(CT.quantite) AS group_quantite
FROM ct AS CT 
JOIN sw AS SW ON CT.id_sw = SW.id_sw 
JOIN cw AS CW ON CT.id_sw = CW.id_cw
WHERE SW.`dateCrea_sw` 
BETWEEN UNIX_TIMESTAMP('2022-01-01 00:00:00') AND UNIX_TIMESTAMP('2022-12-31 23:59:59') 
AND CT.id_format IN (10, 11, 12) 
AND CT.quantite > 0
AND CW.suivie_cw = 1 
GROUP BY CT.id_produit
WITH ROLLUP;

执行后，结果最后一行的id_produit为NULL，对应的group_quantite就是所有分组的quantite总和。

说明

• 方法1逻辑直观，适合需要先确认分组结果再求和的场景；
• 方法2无需额外子查询，性能更优，同时保留分组明细和汇总结果。

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