SQL中GROUP BY WITH ROLLUP和GROUPING 函数的使用

• WITH ROLLUP：是 SQL 的分组汇总扩展，用于在分组的基础上生成各级别的汇总行，能方便地得到每个分组以及所有分组的总计数据。
• GROUPING 函数：用于标识某一行是否是由 ROLLUP 生成的汇总行，返回 0 表示是原始数据行，返回 1 表示是汇总行。在 ORDER BY 中使用，可控制结果集的排序，让汇总行以特定顺序呈现。

 

语法结构

SELECT 列1,列2,聚合函数(列3) AS 聚合结果,GROUPING(列1) AS 列1是否为汇总行,GROUPING(列2) AS 列2是否为汇总行
FROM 表名
GROUP BY 列1, 列2 WITH ROLLUP
ORDER BY GROUPING(列1) DESC,列1,GROUPING(列2) DESC,列2;

SELECT ori.org_name,p.product_name,SUM(s.sales_amount) AS total_sales,GROUPING(ori.org_name) AS group_org,GROUPING(p.product_name) AS group_product
FROM sales s
JOIN org_info ori ON s.org_id = ori.org_id
JOIN product p ON s.product_id = p.product_id
WHERE s.created_time <= '2025-11-05 23:59:59'
GROUP BY ori.org_name, p.product_name WITH ROLLUP
ORDER BY GROUPING(ori.org_name) DESC,ori.org_name,GROUPING(p.product_name) DESC,p.product_name;

步骤 2：结果解读

• 原始分组行：group_org 和 group_product 都为 0，表示这是某机构某产品的具体销售数据。
• 机构级汇总行：group_org 为 0，group_product 为 1，表示该机构所有产品的销售总额。
• 全量汇总行：group_org 和 group_product 都为 1，表示所有机构所有产品的销售总额。

通过这种方式，你可以高效地获取明细数据和多级汇总数据，无需多次编写单独的汇总查询。

 

实际示例：查询订单信息，根据订单号显示，根据渠道id分组返回小计（汇总行）

其中使用 CASE WHEN GROUPING(t1.orderNo) = 1 THEN '小计' ELSE MAX(t1.channelName) END AS channelName来显示。（是汇总行则显示小计，否则显示原来的渠道名称）

SELECT CASE WHEN GROUPING(t1.orderNo) = 1 THEN NULL ELSE MAX(t1.orderSource) END AS orderSource,CASE WHEN GROUPING(t1.orderNo) = 1 THEN NULL ELSE MAX(t1.channelType) END AS channelType,CASE WHEN GROUPING(t1.orderNo) = 1 THEN NULL ELSE MAX(t1.channelId) END AS channelId,CASE WHEN GROUPING(t1.orderNo) = 1 THEN '小计' ELSE MAX(t1.channelName) END AS channelName,CASE WHEN GROUPING(t1.orderNo) = 1 THEN NULL ELSE t1.orderNo END AS orderNo,SUM(t1.normalVerifyNum + t1.forceVerifyNum + t1.overVerifyNum) AS totalVerifyNum,FORMAT(ROUND(SUM(t1.normalVerifyPrice + t1.forceVerifyPrice + t1.overVerifyPrice), 2), 2) AS totalVerifyPrice,GROUPING(t1.channelId) AS isChannelRollup,GROUPING(t1.orderNo) AS isOrderRollup
FROM (SELECT o.order_source AS orderSource,o.order_channel AS channelType,oi.id channelId,oi.org_name channelName,o.order_no orderNo,o.third_order_no AS thirdOrderNo,o.product_type AS productType,p.product_name AS productName,pp.name AS policyName,CASE WHEN ot.verify_status = 'true' AND ot.offline_verify_sync_status IS NULL THEN 1 ELSE 0 END AS normalVerifyNum,CASE WHEN ot.verify_status = 'true' AND ot.offline_verify_sync_status IS NULL THEN ROUND(IFNULL(ot.act_price, opi.price), 2) ELSE '0.00' END AS normalVerifyPrice,CASE WHEN ot.verify_status = 'true' AND ot.offline_verify_sync_status IS NOT NULL THEN 1 ELSE 0 END AS forceVerifyNum,CASE WHEN ot.verify_status = 'true' AND ot.offline_verify_sync_status IS NOT NULL THEN ROUND(IFNULL(ot.act_price, opi.price), 2) ELSE '0.00' END AS forceVerifyPrice,CASE WHEN ot.verify_status = 'overdue' THEN 1 ELSE 0 END AS overVerifyNum,CASE WHEN ot.verify_status = 'overdue' THEN ROUND(IFNULL(ot.act_price, opi.price), 2) ELSE '0.00' END AS overVerifyPrice,DATE_FORMAT(ot.visit_date, '%Y-%m-%d') playDate,DATE_FORMAT(ot.verify_time, '%Y-%m-%d') verifyDate,ovp.serial_no serialNo,ov.visitor_name AS visitorName,ov.phone_number AS phoneNumber,ov.document_type AS documentType,ov.document_code AS documentCode,ov.email,o.order_type AS orderType,o.payment_type AS paymentType,c.org_name companyName,a.nick_name AS nickName,DATE_FORMAT(ot.verify_time, '%Y-%m-%d %H:%i:%s') AS verifyTime,DATE_FORMAT(o.created_time, '%Y-%m-%d %H:%i:%s') AS place_order_time,opi.price AS priceFROM order_tickets otLEFT JOIN order_info o ON ot.order_id = o.idLEFT JOIN org_info oi ON o.channel_id = oi.idLEFT JOIN order_visitor_product ovp ON ovp.id = ot.visitor_product_idLEFT JOIN product p ON p.id = ot.product_idLEFT JOIN order_product_info opi ON opi.id = ot.order_product_idLEFT JOIN product_policy pp ON pp.id = opi.policy_idLEFT JOIN order_visitor ov ON ov.id = ot.visitor_idLEFT JOIN org_info c ON c.id = o.org_sale_idLEFT JOIN account a ON a.id = o.salesmanWHERE 1=1AND ot.org_sale_id = 1898978223795081217AND ot.verify_status IN ('overdue', 'true')AND ot.verify_time >= DATE_FORMAT('2025-10-01 00:00:00', '%Y-%m-%d %H:%i:%s')AND ot.verify_time <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')AND o.payment_status = 'true'AND o.is_deleted = 'false'AND o.order_status != 'closed'AND o.audit_status = 'success'AND o.parent_pck_id IS NULLAND o.parent_product_type IS NULL
) t1
GROUP BY t1.channelId, t1.orderNo WITH ROLLUP 
HAVING GROUPING(t1.channelId) = 0
ORDER BY t1.channelId, isOrderRollup, MAX(t1.verifyTime) DESC;