MySQL ROLLUP 基本指南（通过实际示例）

摘要：在本教程中，您将学习如何使用 MySQL ROLLUP子句生成小计和总计。

设置样本表

以下语句创建一个名为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);
Code language: SQL (Structured Query Language) (sql)

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

SELECT * FROM sales;Code language: SQL (Structured Query Language) (sql)

MySQL 汇总概述

分组集是要分组的一组列。例如，以下查询创建一个由(productline)表示的分组集

SELECT 
    productline, 
    SUM(orderValue) totalOrderValue
FROM
    sales
GROUP BY 
    productline;Code language: SQL (Structured Query Language) (sql)

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

SELECT 
    SUM(orderValue) totalOrderValue
FROM
    sales;Code language: SQL (Structured Query Language) (sql)

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

SELECT 
    productline, 
    SUM(orderValue) totalOrderValue
FROM
    sales
GROUP BY 
    productline 
UNION ALL
SELECT 
    NULL, 
    SUM(orderValue) totalOrderValue
FROM
    sales;Code language: SQL (Structured Query Language) (sql)

这是查询输出：

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

productLine列中的NULL标识总计超级聚合行。

此查询能够按产品线生成总订单值以及总计行。然而，它有两个问题：

查询相当冗长。
查询的性能可能不好，因为数据库引擎必须在内部执行两个单独的查询并将结果集合并为一个。

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

ROLLUP子句是GROUP BY子句的扩展，语法如下：

SELECT 
    select_list
FROM 
    table_name
GROUP BY
    c1, c2, c3 WITH ROLLUP;
Code language: SQL (Structured Query Language) (sql)

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

SELECT 
    productLine, 
    SUM(orderValue) totalOrderValue
FROM
    sales
GROUP BY 
    productline WITH ROLLUP;Code language: SQL (Structured Query Language) (sql)

这是输出：

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

如果在GROUP BY子句中指定了多个列，则ROLLUP子句假定输入列之间存在层次结构。

例如：

GROUP BY c1, c2, c3 WITH ROLLUPCode language: SQL (Structured Query Language) (sql)

ROLLUP假设存在以下层次结构：

c1 > c2 > c3Code language: SQL (Structured Query Language) (sql)

它生成以下分组集：

(c1, c2, c3)
(c1, c2)
(c1)
()Code language: SQL (Structured Query Language) (sql)

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

GROUP BY c1, c2 WITH ROLLUPCode language: SQL (Structured Query Language) (sql)

那么ROLLUP会生成以下分组集：

(c1, c2)
(c1)
()Code language: SQL (Structured Query Language) (sql)

请参阅以下查询示例：

SELECT 
    productLine, 
    orderYear,
    SUM(orderValue) totalOrderValue
FROM
    sales
GROUP BY 
    productline, 
    orderYear 
WITH ROLLUP;Code language: SQL (Structured Query Language) (sql)

这是输出：

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

本例中的层次结构是：

productLine > orderYearCode language: SQL (Structured Query Language) (sql)

如果颠倒层次结构，例如：

SELECT 
    orderYear,
    productLine, 
    SUM(orderValue) totalOrderValue
FROM
    sales
GROUP BY 
    orderYear,
    productline
WITH ROLLUP;Code language: SQL (Structured Query Language) (sql)

下图显示了输出：

每次年份更改时， ROLLUP都会生成小计，并在结果集末尾生成总计。

本例中的层次结构是：

orderYear > productLine
Code language: SQL (Structured Query Language) (sql)

GROUPING() 函数

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

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

GROUPING()函数可用于选择列表、 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;Code language: SQL (Structured Query Language) (sql)

下图显示了输出：

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

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

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

以下示例演示如何将IF()函数与GROUPING()函数结合起来，以用标签替换orderYear和productLine列中的超级聚合NULL值：

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;Code language: SQL (Structured Query Language) (sql)

输出是：

MySQL ROLLUP - GROUPING function substitution

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

本教程有帮助吗？