MySQL编程进阶之：`GROUP BY`的高级用法：`WITH ROLLUP`和`GROUPING SETS`的实践。

各位观众老爷，大家好！我是你们的老朋友，今天咱们来聊聊MySQL里GROUP BY的高级玩法：WITH ROLLUP和GROUPING SETS。这俩哥们，用好了能让你在数据分析的道路上如虎添翼，事半功倍！

一、WITH ROLLUP：汇总界的扛把子

先说说WITH ROLLUP，这家伙的功能很简单粗暴：在分组的基础上，给你加上各种维度的汇总数据。说白了，就是帮你把小计、总计一股脑儿算出来。

1. 基本用法：

咱们先来个例子，假设我们有个sales表，记录了每个城市、每个产品的销售额：

CREATE TABLE sales (
    city VARCHAR(50),
    product VARCHAR(50),
    sales INT
);

INSERT INTO sales (city, product, sales) VALUES
('北京', '手机', 100),
('北京', '电脑', 200),
('上海', '手机', 150),
('上海', '电脑', 250),
('广州', '手机', 120),
('广州', '电脑', 180);

如果我们想统计每个城市的总销售额，很简单，GROUP BY一下：

SELECT city, SUM(sales) AS total_sales
FROM sales
GROUP BY city;

结果：

city	total_sales
北京	300
上海	400
广州	300

现在，我们要加上总计，也就是所有城市的总销售额。这时候，WITH ROLLUP就派上用场了：

SELECT city, SUM(sales) AS total_sales
FROM sales
GROUP BY city WITH ROLLUP;

结果：

city	total_sales
北京	300
上海	400
广州	300
NULL	1000

看到没？最后一行city变成了NULL，total_sales变成了1000，这就是总计。WITH ROLLUP会在所有分组的基础上，再加一行汇总数据。

2. 多字段分组：

WITH ROLLUP更强大的地方在于，它可以处理多字段分组。比如，我们想统计每个城市、每个产品的销售额，以及每个城市总销售额，最后再来个总计：

SELECT city, product, SUM(sales) AS total_sales
FROM sales
GROUP BY city, product WITH ROLLUP;

结果：

city	product	total_sales
北京	手机	100
北京	电脑	200
北京	NULL	300
上海	手机	150
上海	电脑	250
上海	NULL	400
广州	手机	120
广州	电脑	180
广州	NULL	300
NULL	NULL	1000

注意看，WITH ROLLUP会从最右边的分组字段开始，依次向上汇总。

• 先是product变成NULL，表示每个城市总销售额；
• 然后city和product都变成NULL，表示总销售额。

3. IFNULL函数：

上面的结果里，NULL看起来有点不舒服，我们可以用IFNULL函数把它替换成更友好的字符串：

SELECT IFNULL(city, '总计') AS city, IFNULL(product, '总计') AS product, SUM(sales) AS total_sales
FROM sales
GROUP BY city, product WITH ROLLUP;

结果：

city	product	total_sales
北京	手机	100
北京	电脑	200
北京	总计	300
上海	手机	150
上海	电脑	250
上海	总计	400
广州	手机	120
广州	电脑	180
广州	总计	300
总计	总计	1000

看起来是不是顺眼多了？

4. GROUPING函数：

有时候，我们不仅要显示汇总数据，还想知道这行数据是哪个维度汇总出来的。这时候，GROUPING函数就派上用场了。GROUPING(column)函数会返回一个整数：

• 如果column是汇总出来的，返回1；
• 如果column是原始数据，返回0。

SELECT city, product, SUM(sales) AS total_sales, GROUPING(city), GROUPING(product)
FROM sales
GROUP BY city, product WITH ROLLUP;

结果：

city	product	total_sales	GROUPING(city)	GROUPING(product)
北京	手机	100	0	0
北京	电脑	200	0	0
北京	NULL	300	0	1
上海	手机	150	0	0
上海	电脑	250	0	0
上海	NULL	400	0	1
广州	手机	120	0	0
广州	电脑	180	0	0
广州	NULL	300	0	1
NULL	NULL	1000	1	1

可以看到，city和product都是NULL的那一行，GROUPING(city)和GROUPING(product)都是1，表示这行是总计。

我们可以利用GROUPING函数来更灵活地显示汇总信息：

SELECT
    CASE
        WHEN GROUPING(city) = 1 THEN '总计'
        ELSE IFNULL(city, '所有城市')
    END AS city,
    CASE
        WHEN GROUPING(product) = 1 THEN '总计'
        ELSE IFNULL(product, '所有产品')
    END AS product,
    SUM(sales) AS total_sales
FROM sales
GROUP BY city, product WITH ROLLUP;

这个例子里，我们用CASE语句判断GROUPING函数的返回值，根据不同的维度显示不同的汇总信息。

二、GROUPING SETS：定制你的汇总需求

WITH ROLLUP虽然强大，但也有局限性。它只能按照分组字段从右向左依次汇总，不能定制汇总的维度。比如，我们只想统计每个城市总销售额，以及每个产品总销售额，但不需要总计。这时候，GROUPING SETS就闪亮登场了。

1. 基本用法：

GROUPING SETS允许你指定多个分组集合，每个集合都会生成一个汇总结果。

还是用上面的sales表，我们想统计每个城市总销售额，以及每个产品总销售额：

SELECT city, product, SUM(sales) AS total_sales
FROM sales
GROUP BY GROUPING SETS ((city), (product));

结果：

city	product	total_sales
北京	NULL	300
上海	NULL	400
广州	NULL	300
NULL	手机	370
NULL	电脑	630

可以看到，结果包含了每个城市总销售额（city分组）和每个产品总销售额（product分组），但没有总计。

2. 多字段分组：

GROUPING SETS也可以处理多字段分组。比如，我们想统计：

• 每个城市、每个产品的销售额；
• 每个城市总销售额；
• 每个产品总销售额。

SELECT city, product, SUM(sales) AS total_sales
FROM sales
GROUP BY GROUPING SETS ((city, product), (city), (product));

结果：

city	product	total_sales
北京	手机	100
北京	电脑	200
上海	手机	150
上海	电脑	250
广州	手机	120
广州	电脑	180
北京	NULL	300
上海	NULL	400
广州	NULL	300
NULL	手机	370
NULL	电脑	630

3. 空集：

GROUPING SETS还可以包含空集()，表示不分组，直接计算总计：

SELECT city, product, SUM(sales) AS total_sales
FROM sales
GROUP BY GROUPING SETS ((city, product), (city), (product), ());

结果：

city	product	total_sales
北京	手机	100
北京	电脑	200
上海	手机	150
上海	电脑	250
广州	手机	120
广州	电脑	180
北京	NULL	300
上海	NULL	400
广州	NULL	300
NULL	手机	370
NULL	电脑	630
NULL	NULL	1000

4. CUBE和ROLLUP的等价写法：

GROUPING SETS实际上可以模拟CUBE和ROLLUP的功能。

• ROLLUP(city, product)等价于GROUPING SETS ((city, product), (city), ())
• CUBE(city, product)等价于GROUPING SETS ((city, product), (city), (product), ())

CUBE会生成所有可能的分组组合，而ROLLUP只会从右向左依次汇总。

三、WITH ROLLUP vs GROUPING SETS：

特性	WITH ROLLUP	GROUPING SETS
功能	从右向左依次汇总	可以指定多个分组集合，定制汇总维度
灵活性	较低	较高
适用场景	需要完整的层级汇总结构时	需要定制特定维度的汇总时
是否包含总计	默认包含总计	可以通过包含空集()来包含总计

总结：

• WITH ROLLUP适合需要完整层级汇总结构的场景，比如生成报表时，需要包含小计、总计等。
• GROUPING SETS适合需要定制特定维度汇总的场景，比如只需要统计每个城市总销售额和每个产品总销售额，不需要总计。

掌握了WITH ROLLUP和GROUPING SETS，你就能更灵活地进行数据分析，挖掘出更多有价值的信息。 以后面试再问你GROUP BY，你就把这些东西甩给面试官，直接起飞！

好了，今天的分享就到这里，希望对大家有所帮助！下次有机会再和大家聊聊MySQL的其他高级特性。 拜拜！