举例group by grouping sets

假设有以下表格：

CREATE TABLE sales (
    region VARCHAR(10),
    product VARCHAR(10),
    month VARCHAR(10),
    amount INT
);

INSERT INTO sales VALUES ('North', 'Apple', 'January', 100);
INSERT INTO sales VALUES ('North', 'Banana', 'January', 200);
INSERT INTO sales VALUES ('North', 'Apple', 'February', 150);
INSERT INTO sales VALUES ('North', 'Banana', 'February', 250);
INSERT INTO sales VALUES ('South', 'Apple', 'January', 120);
INSERT INTO sales VALUES ('South', 'Banana', 'January', 180);
INSERT INTO sales VALUES ('South', 'Apple', 'February', 130);
INSERT INTO sales VALUES ('South', 'Banana', 'February', 230);

现在我们想要根据 region 和 month 进行分组，并且还想要额外的汇总数据。可以使用 grouping sets 实现：

SELECT 
    region, 
    month, 
    SUM(amount) AS total_sales 
FROM 
    sales 
GROUP BY 
    GROUPING SETS((region, month), region, month, ())
ORDER BY 
    region, 
    month;

这个查询会返回以下结果：

+--------+---------+-------------+
| region |  month  | total_sales |
+--------+---------+-------------+
| North  |         |         700 |
| North  |        |         700 |
| North  |  February |         400 |
| North  |  January  |         300 |
| South  |         |         760 |
| South  |        |         760 |
| South  |  February |         360 |
| South  |  January  |         400 |
+--------+---------+-------------+

我们可以看到，这个查询返回了所有可能的组合，包括 region 和 month 的汇总数据，以及全局的汇总数据。