MySQL ROLLUP-之路教程

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;

下图显示了输出：

当在超聚合行中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;

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

创建实例表

以下语句创建一个名为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

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

MySQL ROLLUP概述

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

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

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

SELECT 
    SUM(orderValue) totalOrderValue
FROM
    sales;

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

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

这是查询输出：

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

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

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

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

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

ROLLUP子句是GROUP BY子句的扩展，具有以下语法：

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

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

请参阅以下查询：

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

这是输出：

从输出中可以清楚地看到，ROLLUP子句不仅生成小计，还生成订单值的总计。

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

例如：

GROUP BY c1, c2, c3 WITH ROLLUP

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

c1 > c2 > c3

并生成以下分组集：

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

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

GROUP BY c1, c2 WITH ROLLUP

然后ROLLUP会生成以下分组集：

(c1, c2)
(c1)
()

请参阅以下查询示例：

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

这是输出：

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

在这种情况下，层次结构为：

productLine > orderYear

如果您反转层次结构，例如：

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

下图显示了输出：

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

本示例中的层次结构为：

orderYear > productLine

日期：2019-11-20 08:52:06 来源：oir作者：oir

←MySQL HAVING子句

MySQL子查询→

GROUPING()函数

创建实例表

MySQL ROLLUP概述

目录