记录一下mysql 的WITH ROLLUP 用法

在 MySQL 中，WITH ROLLUP 是一个用于在 GROUP BY 查询中进行数据汇总的选项。它会生成包含子总计行和总计行的结果集。

当使用 WITH ROLLUP 时，MySQL 会为每个指定的 GROUP BY 列生成一行子总计，并在最后添加一行总计。这些子总计和总计行的值是基于当前查询结果集中的分组的总计。

以下是 WITH ROLLUP 的使用示例：

select CASE WHEN order_no IS NULL THEN '合计' ELSE order_no END AS order_no ,
        CASE WHEN order_no IS NULL THEN NULL ELSE customs_date END AS customs_date,
        CASE WHEN order_no IS NULL THEN NULL ELSE customs_no END AS customs_no,
        CASE WHEN order_no IS NULL THEN NULL ELSE business_company END AS business_company,
        CASE WHEN order_no IS NULL THEN NULL ELSE trans_bill_no END AS trans_bill_no,ie_flag,
        SUM(CASE WHEN fee_name = '报关费' THEN total_amount ELSE 0 END) AS 报关费,
        SUM(CASE WHEN fee_name = '报检费' THEN total_amount ELSE 0 END) AS 报检费,
        teu,
        SUM(CASE WHEN fee_name = '卡口进场理箱费' THEN total_amount ELSE 0 END) AS 卡口进场理箱费,
        SUM(CASE WHEN fee_name = '装卸费' THEN total_amount ELSE 0 END) AS 装卸费,
        SUM(CASE WHEN fee_name = '代理费' THEN total_amount ELSE 0 END) AS 代理费,
        SUM(total_amount) AS 小计
        from (
        SELECT
        a.order_no,
        a.customs_date,
        a.customs_no,
        a.business_company,
        a.trans_bill_no,
        a.teu,
        b.fee_type,
        b.fee_code,
        b.fee_name,
        b.total_amount,
        a.ie_flag
        FROM
        biz_order_customs AS a
        LEFT JOIN biz_order_fee AS b ON a.order_no = b.order_no
                ) x
        group by order_no WITH ROLLUP