以下内容仅是站长或网友个人学习笔记、总结和研究收藏。不保证正确性
来源于
易百教程
- SQL ROLLUP简介ROLLUP是GROUP BY子句的扩展。 ROLLUP选项允许包含表示小计的额外行,通常称为超级聚合行,以及总计行。 通过使用ROLLUP选项,可以使用单个查询生成多个分组集。
使用语法:group by rollup(a,b)
说明:针对 a 列、b 列分别进行分组,再针对 (a,b) 组合列进行分组,最后汇总。
注意,分组集是一组用于分组的列。 例如,一个由仓库返回库存的查询,分组集是(warehouse)。
SELECT
warehouse,
SUM(quantity) qty
FROM
inventory
GROUP BY
warehouse;
以下是SQL ROLLUP的基本语法:
SELECT
c1, c2, aggregate_function(c3)
FROM
table
GROUP BY ROLLUP (c1, c2);
ROLLUP假定输入列之间存在层次结构。 例如,如果输入列是(c1,c2),则层次结构c1> c2。 ROLLUP生成考虑此层次结构有意义的所有分组集。 这就是为什么我们经常使用ROLLUP来生成小计和总计以用于报告目的。
在上面的语法中,ROLLUP(c1,c2)生成以下三个分组集:
(c1,c2)
(c1)
()
Oracle,Microsoft SQL Server和PostgreSQL支持此语法。 但是,MySQL的语法略有不同,如下所示:
SELECT
c1, c2, aggregate_function(c3)
FROM
table_name
GROUP BY c1, c2 WITH ROLLUP;
- SQL ROLLUP示例我们将使用在GROUPING SETS教程中设置的inventory表进行演示。
mysql> select * from inventory;
+---------------+---------+----------+----------+
| warehouse | product | model | quantity |
+---------------+---------+----------+----------+
| San Jose | iPhone | 6s | 100 |
| San Fransisco | iPhone | 6s | 50 |
| San Jose | iPhone | 7 | 50 |
| San Fransisco | iPhone | 7 | 10 |
| San Jose | iPhone | X | 150 |
| San Fransisco | iPhone | X | 200 |
| San Jose | Samsung | Galaxy S | 200 |
| San Fransisco | Samsung | Galaxy S | 200 |
| San Fransisco | Samsung | Note 8 | 100 |
| San Jose | Samsung | Note 8 | 150 |
+---------------+---------+----------+----------+
10 rows in set
2.1. SQL ROLLUP有一个列示例
以下语句使用GROUP BY子句和SUM()函数按仓库查找总库存:
SELECT
warehouse, SUM(quantity)
FROM
inventory
GROUP BY warehouse;
+---------------+---------------+
| warehouse | SUM(quantity) |
+---------------+---------------+
| San Fransisco | 560 |
| San Jose | 650 |
+---------------+---------------+
2 rows in set
要检索所有仓库中的总产品数,请将ROLLUP添加到GROUP BY子句,如下所示:
SELECT
warehouse, SUM(quantity)
FROM
inventory
GROUP BY ROLLUP(warehouse);
执行上面示例代码,得到以下结果:
正如在结果中看到的那样,warehouse列中的NULL值指定了总计超级聚合行。 在此示例中,ROLLUP选项使查询生成另一行,显示所有仓库中的总产品数量。要使输出更具可读性,可以使用COALESCE()函数将NULL值替换All值,如下所示:
SELECT
COALESCE(warehouse, 'All warehouses') AS warehouse,
SUM(quantity)
FROM
inventory
GROUP BY ROLLUP (warehouse);
执行上面示例代码,得到以下结果:
2.2. SQL ROLLUP有多列示例
以下语句按warehouse和product计算库存:
SELECT
warehouse, product, SUM(quantity)
FROM
inventory
GROUP BY warehouse, product;
执行上面示例代码,得到以下结果:
将ROLLUP添加到GROUP BY子句:
SELECT
warehouse, product, SUM(quantity)
FROM
inventory
GROUP BY ROLLUP (warehouse , product);
执行上面示例代码,得到以下结果:
请注意,输出包含两个分析级别的摘要信息,而不仅仅是一个:
在指定仓库的每组产品行之后,将显示一个额外的摘要行,显示总库存。 在这些行中,product列中的值设置为NULL。在所有行之后,将显示一个额外的摘要行,显示所有仓库和产品的总库存。 在这些行中,warehouse 和 product 列中的值设置为NULL。
2.3. SQL ROLLUP带有部分汇总的示例
可以使用ROLLUP执行部分汇总,以减少计算的小计数,如以下示例所示:
SELECT
warehouse, product, SUM(quantity)
FROM
inventory
GROUP BY warehouse, ROLLUP (product);
执行上面示例代码,得到以下结果:
在此示例中,ROLLUP仅为product列而不是warehouse列生成超级聚合摘要。
在本教程中,您学习了如何使用SQL ROLLUP通过单个查询执行多级分析。