MySQL汇总

摘要：在本教程中，您将学习如何使用 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);

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

SELECT * FROM sales;

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 在第二个查询的选择列表中添加以满足此要求。

将 NULL 在 productLine 列标识总计超总量线。

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

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

要解决这些问题，您可以使用 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

GROUPING() 函数

要检查 NULL 结果集中是小计还是总计，请使用该 GROUPING() 函数。

GROUPING() 当 NULL 出现在超聚合行中时，该函数返回 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;

下图显示了输出：

的 GROUPING(orderYear) 时返回1 NULL 在 orderYear 柱发生在超总量行，否则为0。

类似地， GROUPING(productLine) 当返回1 NULL 在 productLine 柱发生在超总量行，否则为0。

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

以下实例显示了如何将 IF() 函数与 GROUPING() 函数结合起来以替换和列中的超级聚合 NULL 值的标签： 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() 生成多个分组集，考虑 GROUP BY 子句中指定的列之间的层次结构。# MySQL汇总

设置实例表

MySQL ROLLUP 概述

GROUPING() 函数

本页目录