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 price
    FROM order_tickets ot
    LEFT JOIN order_info o ON ot.order_id = o.id
    LEFT JOIN org_info oi ON o.channel_id = oi.id
    LEFT JOIN order_visitor_product ovp ON ovp.id = ot.visitor_product_id
    LEFT JOIN product p ON p.id = ot.product_id
    LEFT JOIN order_product_info opi ON opi.id = ot.order_product_id
    LEFT JOIN product_policy pp ON pp.id = opi.policy_id
    LEFT JOIN order_visitor ov ON ov.id = ot.visitor_id
    LEFT JOIN org_info c ON c.id = o.org_sale_id
    LEFT JOIN account a ON a.id = o.salesman
    WHERE 1=1
      AND ot.org_sale_id = 1898978223795081217
      AND 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 NULL
      AND 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;