Mysql 简明教程
MySQL - ROLLUP
The MySQL ROLLUP Clause
MySQL ROLLUP 子句是 GROUP BY 子句的扩展。它与 MySQL 中的聚合函数一起使用，以在表内的额外行中找到总计或列值的汇总（也称为该列的超聚合）。
The MySQL ROLLUP Clause is an extension of the GROUP BY Clause. It is used with aggregate functions in MySQL to find the grand total or a summary of a column’s values (also known as super-aggregate of a column), in an extra row within a table.
考虑一家制造工厂在表中跟踪月度生产数据。若要确定年产量，可以与 ROLLUP 一起使用 SUM() 聚合函数。但是，如果您需要找出产量低于特定阈值的月份数，则 ROLLUP 也允许您使用 COUNT() 函数对这些月份进行计数。
Consider a manufacturing factory that tracks monthly production data in a table. To determine the annual product production, you can use the SUM() aggregate function along with ROLLUP. However, if you need to find out the number of months where production falls below a specific threshold, ROLLUP will allow you to count such months as well using the COUNT() function.
Syntax
以下 MySQL 中 ROLLUP 子句的语法 −
Following is the syntax of ROLLUP clause in MySQL −
SELECT AggregateFunction(column_name(s)), column_name(s)
FROM table_name
GROUP BY column_name(s)
WITH ROLLUP;Example
首先，我们将创建一个名为 "PRODUCT" 的表，其中包含生产信息，例如产品 ID、产品名称、产品数量以及组织内的生产月份 −
First, we will create a table named "PRODUCT" containing production information such as product ID, product name, product count, and manufacturing month within an organization −
CREATE TABLE PRODUCT (
PRODUCT_ID INT,
PRODUCT_NAME VARCHAR(50),
PRODUCT_COUNT INT,
MONTH VARCHAR(20)
);现在，让我们向上述创建的表中插入一些数据 −
Now, let us insert some data into the above-created table −
INSERT INTO PRODUCT VALUES
(101, 'Comb', 2345, 'January'),
(102, 'Coffee Mugs', 1242, 'January'),
(103, 'Cutlery', 124, 'January'),
(101, 'Comb', 3263, 'February'),
(102, 'Coffee Mugs', 10982, 'February'),
(103, 'Cutlery', 435, 'February');获得的 PRODUCT 表如下 −
The PRODUCT table obtained is as follows −
现在，让我们如下所示使用 ROLLUP 找出每月生产的产品总数 −
Now, let us to find the sum of products manufactured each MONTH using ROLLUP as shown below −
SELECT SUM(PRODUCT_COUNT), MONTH
FROM PRODUCT
GROUP BY MONTH WITH ROLLUP;ROLLUP on Multiple Columns
您还可以使用 GROUP BY 子句对同一组中多个列进行 ROLLUP。
You can also use ROLLUP on multiple columns by grouping them together using GROUP BY clause.
Example
在此，我们在 PRODUCT 表的列 "PRODUCT_ID" 和 "PRODUCT_NAME" 上应用 GROUP BY 子句 −
Here, we are applying the GROUP BY clause on columns 'PRODUCT_ID' and 'PRODUCT_NAME' of the PRODUCT table −
SELECT PRODUCT_ID,
COUNT(PRODUCT_ID) AS PROD_ID_COUNT,
PRODUCT_NAME,
COUNT(PRODUCT_NAME) AS PROD_ID_NAME
FROM PRODUCT
GROUP BY PRODUCT_ID, PRODUCT_NAME;我们获得以下输出 −
We get the following output −
现在，使用 ROLLUP 计算这两行的摘要，如下面的查询中所示：
Now, calculate the summary of these two rows using ROLLUP as shown in the following query −
SELECT PRODUCT_ID,
COUNT(PRODUCT_ID) AS PROD_ID_COUNT,
PRODUCT_NAME,
COUNT(PRODUCT_NAME) AS PROD_ID_NAME
FROM PRODUCT
GROUP BY PRODUCT_ID, PRODUCT_NAME
WITH ROLLUP;您可以在下面的输出中看到，摘要不仅在最终级别计算，而且在两个级别计算。对于每个产品名称，都会显示一列摘要：
You can see in the output below that the summary is calculated not only at the final level but also at two levels. For every product name, a column summary is displayed −