Skip to main content

MySQL汇总实用示例的基本指南

设置示例表

下面的语句创建了一个名为sales的新表,该表存储按产品线和年份汇总的订单值。数据来自示例数据库中的products、orders和orderDetails表。

CREATE TABLE sales
SELECT
productLine,
YEAR(orderDate) orderYear,
SUM(quantityOrdered * priceEach) orderValue
FROM
orderDetails
INNER JOIN
orders USING (orderNumber)
INNER JOIN
products USING (productCode)
GROUP BY
productLine ,
YEAR(orderDate);

以下查询返回sales表中的所有行:

SELECT * FROM sales;

MySQL ROLLUP - sample table

MySQL汇总概述

分组集是要分组到的一组列。例如,下面的查询创建了一个由(productline)表示的分组集

SELECT 
productline,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
productline;

MySQL ROLLUP - GROUP BY clause

以下查询创建了一个由()表示的空分组集:

SELECT 
SUM(orderValue) totalOrderValue
FROM
sales;

MySQL ROLLUP - Empty Grouping Set

如果要在一个查询中同时生成两个或多个分组集,可以使用UNION ALL运算符,如下所示:

SELECT 
productline,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
productline
UNION ALL
SELECT
NULL,
SUM(orderValue) totalOrderValue
FROM
sales;

以下是查询输出:

MySQL ROLLUP - UNION ALL

由于UNION ALL要求所有查询具有相同的列数,因此我们在第二个查询的select列表中添加了NULL以满足此要求。

productLine列中的NULL表示总计超级聚合行。

此查询可以按产品线生成订单总值,也可以生成总计行。然而,它有两个问题:

要解决这些问题,可以使用ROLLUP子句。

ROLLUP子句是GROUP BY子句的扩展,语法如下:

SELECT 
select_list
FROM
table_name
GROUP BY
c1, c2, c3 WITH ROLLUP;

汇总根据GROUP BY子句中指定的列或表达式生成多个分组集。例如:

SELECT 
productLine,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
productline WITH ROLLUP;

以下是输出:

MySQL ROLLUP example

正如输出中清楚显示的,ROLLUP子句不仅生成小计,还生成订单值的总计。

如果在GROUP BY子句中指定了多个列,ROLLUP子句将在输入列中采用层次结构。

例如:

GROUP BY c1, c2, c3 WITH ROLLUP

汇总假设存在以下层次结构:

c1 > c2 > c3

并生成以下分组集:

(c1, c2, c3)
(c1, c2)
(c1)
()

如果在GROUP BY子句中指定了两列:

GROUP BY c1, c2 WITH ROLLUP

然后汇总生成以下分组集:

(c1, c2)
(c1)
()

请参见以下查询示例:

SELECT 
productLine,
orderYear,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
productline,
orderYear
WITH ROLLUP;

以下是输出:

MySQL ROLLUP - hierarchy

每次产品线更改时,汇总都会生成小计行,并在结果末尾生成总计。

这种情况下的层次结构是:

productLine > orderYear

如果反转层次结构,例如:

SELECT 
orderYear,
productLine,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
orderYear,
productline
WITH ROLLUP;

下图显示了输出:

MySQL ROLLUP - another hierarchy

每次年度发生变化时,汇总都会生成小计,并在结果集结束时生成总计。

本例中的层次结构是:

orderYear > productLine

GROUPING()函数

要检查结果集中的NULL是否表示小计或总计,可以使用GROUPING()函数。

当超级聚合行中出现NULL时,GROUPING()函数返回1,否则返回0。

GROUPING()函数可以在select列表、HAVING子句和(从MySQL 8.0.12开始)ORDER BY子句中使用。

考虑下面的查询:

SELECT 
orderYear,
productLine,
SUM(orderValue) totalOrderValue,
GROUPING(orderYear),
GROUPING(productLine)
FROM
sales
GROUP BY
orderYear,
productline
WITH ROLLUP;

下图显示了输出:

MySQL ROLLUP - GROUPING function example

当超级聚合行中orderYear列中出现NULL时,分组(orderYear)返回1,否则返回0。

类似地,当超级聚合行中productLine列中出现NULL时,分组(productLine)返回1,否则返回0。

我们经常使用GROUPING()函数来替换有意义的标签,而不是直接显示它。

下面的示例显示了如何将IF()函数与GROUPING()函数结合起来,以替换orderYear和productLine列中的超级聚合空值的标签:

SELECT 
IF(GROUPING(orderYear),
'All Years',
orderYear) orderYear,
IF(GROUPING(productLine),
'All Product Lines',
productLine) productLine,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
orderYear ,
productline
WITH ROLLUP;

输出为:

MySQL ROLLUP - GROUPING function substitution

在本教程中,您学习了如何使用MySQL ROLLUP()生成多个分组集,考虑GROUP BY子句中指定的列之间的层次结构。