MySQL - ROLLUP

MySQL ROLLUP 子句
基于多列的 ROLLUP
使用客户端程序进行汇总

MySQL ROLLUP 子句

MySQL ROLLUP 子句是 GROUP BY 子句的扩展。它与 MySQL 中的聚合函数一起使用，用于在表中的额外行中查找列值的总计或汇总(也称为列的超级聚合)。

假设有一家制造工厂，它在表中跟踪月度生产数据。要确定年度产品产量，您可以将 SUM() 聚合函数与 ROLLUP 结合使用。但是，如果您需要找出产量低于特定阈值的月份数，ROLLUP 还允许您使用 COUNT() 函数来统计这些月份。

语法

以下是 MySQL 中 ROLLUP 子句的语法 -

SELECT AggregateFunction(column_name(s)), column_name(s)
FROM table_name
GROUP BY column_name(s)
WITH ROLLUP;

示例

首先，我们将创建一个名为"PRODUCT"的表，其中包含组织内的生产信息，例如产品 ID、产品名称、产品数量和生产月份 -

CREATE TABLE PRODUCT (
   PRODUCT_ID INT,
   PRODUCT_NAME VARCHAR(50),
   PRODUCT_COUNT INT,
   MONTH VARCHAR(20)
);

现在，让我们向上面创建的表中插入一些数据 -

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表如下-

PRODUCT_ID	PRODUCT_NAME	PRODUCT_COUNT	MONTH
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

现在，让我们使用 ROLLUP 函数计算每个月生产的产品总数，如下所示 -

SELECT SUM(PRODUCT_COUNT), MONTH
FROM PRODUCT
GROUP BY MONTH WITH ROLLUP;

输出

您可以在下面的输出中看到，一月和二月的单个产品数量均已计算，并且使用 ROLLUP 函数在第三行显示了总产量的总计 -

SUM(PRODUCT_COUNT)	MONTH
14680	February
3711	January
18391	NULL

对多列执行 ROLLUP 操作

您还可以使用 GROUP BY 子句对多列进行分组，从而实现 ROLLUP 操作。

示例

此处，我们将 GROUP BY 子句应用于 PRODUCT 表的"PRODUCT_ID"和"PRODUCT_NAME"列 -

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;

我们得到以下输出 -

PRODUCT_ID	PROD_ID_COUNT	PRODUCT_NAME	PROD_ID_NAME
101	2	Comb	2
102	2	Coffee Mugs	2
103	2	Cutlery	2

现在，使用 ROLLUP 计算这两行的汇总，如下查询所示 -

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;

您可以在下面的输出中看到，汇总不仅在最后一级计算，而且在两级计算。对于每个产品名称，都会显示一列汇总 -

PRODUCT_ID	PROD_ID_COUNT	PRODUCT_NAME	PROD_ID_NAME
101	2	Comb	2
101	2	NULL	2
102	2	Coffee Mugs	2
102	2	NULL	2
103	2	Cutlery	2
103	2	NULL	2
NULL	6	NULL	6

使用客户端程序进行汇总

我们也可以使用客户端程序进行汇总。

语法

PHP NodeJS Java Python

要通过 PHP 程序使用带有聚合函数的 ROLLUP 计算某一列的总计，我们需要使用 mysqli 函数 query() 执行"SELECT"语句，如下所示 -

$sql = "SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP";
$mysqli->query($sql);

要通过 JavaScript 程序使用带有聚合函数的 ROLLUP 计算某一列的总计，我们需要使用 mysql2 库的 query() 函数执行"SELECT"语句，如下所示 -

sql = "SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP";
con.query(sql);

要通过 Java 程序使用带有聚合函数的 ROLLUP 计算某一列的总计，我们需要使用 JDBC 函数 executeQuery() 执行"SELECT"语句，如下所示 -

String sql = "SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP";
statement.executeQuery(sql);

要通过 Python 程序使用带有聚合函数的 ROLLUP 计算某一列的总计，我们需要使用 MySQL Connector/Python 的 execute() 函数执行"SELECT"语句，如下所示 -

rollup_query = "SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP"
cursorObj.execute(rollup_query)

示例

以下是程序 -

PHP NodeJS Java Python

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.
');
$sql = "CREATE TABLE PRODUCT ( PRODUCT_ID INT, PRODUCT_NAME VARCHAR(50), PRODUCT_COUNT INT, MONTH VARCHAR(20) )";
if($mysqli->query($sql)){
    printf("Product table created successfully....!");
}
//now let's insert some records into the table
$sql = "INSERT INTO PRODUCT VALUES(101, 'Comb', 2345, 'January')";
if($mysqli->query($sql)){
    printf("First record inserted successfully...!
");
}
$sql = "INSERT INTO PRODUCT VALUES(102, 'Coffee Mugs', 1242, 'January')";
if($mysqli->query($sql)){
    printf("Second record inserted successfully...!
");
}
$sql = "INSERT INTO PRODUCT VALUES(103, 'Cutlery', 124, 'January')";
if($mysqli->query($sql)){
    printf("Third record inserted successfully...!
");
}
$sql = "INSERT INTO PRODUCT VALUES(101, 'Comb', 3263, 'February')";
if($mysqli->query($sql)){
    printf("Fourth record inserted successfully...!
");
}
//display the table records
$sql = "SELECT * FROM PRODUCT";
if($result = $mysqli->query($sql)){
    printf("Table records: 
");
while($row = mysqli_fetch_array($result)){
    printf("PRODUCT_ID: %d, PRODUCT_NAME: %s, PRODUCT_COUNT: %d, MONTH: %s",
    $row['PRODUCT_ID'],
    $row['PRODUCT_NAME'],
    $row['PRODUCT_COUNT'],
    $row['MONTH']);
    printf("
");
}}
//let's find the sum of product
$sql = "SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP";
if($result = $mysqli->query($sql)){
    printf("Sum of product: 
");
    while($row = mysqli_fetch_array($result)){
        printf("Sum of product: %d, MONTH: %s", $row['SUM(PRODUCT_COUNT)'], $row['MONTH']);
        printf("
");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

输出

获得的输出如下所示 -

Product table created successfully....!
First record inserted successfully...!
Second record inserted successfully...!
Third record inserted successfully...!
Fourth record inserted successfully...!
Table records: 
PRODUCT_ID: 101, PRODUCT_NAME: Comb, PRODUCT_COUNT: 2345, MONTH: January
PRODUCT_ID: 102, PRODUCT_NAME: Coffee Mugs, PRODUCT_COUNT: 1242, MONTH: January
PRODUCT_ID: 103, PRODUCT_NAME: Cutlery, PRODUCT_COUNT: 124, MONTH: January
PRODUCT_ID: 101, PRODUCT_NAME: Comb, PRODUCT_COUNT: 3263, MONTH: February
Sum of product:
Sum of product: 3263, MONTH: February
Sum of product: 3711, MONTH: January
Sum of product: 6974, MONTH:

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

// Connecting to MySQL
con.connect(function (err) {
    if (err) throw err;
    console.log("Connected!");
    console.log("--------------------------");
   // Create a new database
    sql = "Create Database TUTORIALS";
    con.query(sql);
   sql = "USE TUTORIALS";
    con.query(sql);
   sql = "CREATE TABLE PRODUCT (PRODUCT_ID INT,PRODUCT_NAME VARCHAR(50),PRODUCT_COUNT INT,MONTH VARCHAR(20));"
    con.query(sql);
   sql = "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');"
    con.query(sql);
   sql = "SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP";
    con.query(sql, function(err, result){
      if (err) throw err
      console.log(result);
    });
});

输出

获得的输出如下所示 -

Connected!
--------------------------
[
  { 'SUM(PRODUCT_COUNT)': '14680', MONTH: 'February' },
  { 'SUM(PRODUCT_COUNT)': '3711', MONTH: 'January' },
  { 'SUM(PRODUCT_COUNT)': '18391', MONTH: null }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class RollUp {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //create a table with name Product
            String sql = "CREATE TABLE PRODUCT ( PRODUCT_ID INT, PRODUCT_NAME VARCHAR(50), PRODUCT_COUNT INT, MONTH VARCHAR(20) )";
            st.execute(sql);
            System.out.println("Product table created successfully....!");
            //let's insert some records into it...
            String sql1 = "INSERT INTO PRODUCT VALUES(101, 'Comb', 2345, 'January'),  (102, 'Coffee Mugs', 1242, 'January'), (103, 'Cutlery', 124, 'January'), (101, 'Comb', 3263, 'February')";
            st.execute(sql1);
            System.out.println("Records inserted successfully...!");
            //print table records
            String sql2 = "SELECT * FROM PRODUCT";
            rs = st.executeQuery(sql2);
            System.out.println("Table records: ");
            while(rs.next()) {
                String PRODUCT_ID = rs.getString("PRODUCT_ID");
                String PRODUCT_NAME = rs.getString("PRODUCT_NAME");
                String PRODUCT_COUNT = rs.getString("PRODUCT_COUNT");
                String MONTH = rs.getString("MONTH");
                System.out.println("PRODUCT_ID: " + PRODUCT_ID + ", PRODUCT_NAME: " + PRODUCT_NAME + ", PRODUCT_COUNT: " + PRODUCT_COUNT + ", MONTH: " + MONTH);
            }
            //let's calculate the sum of product with RollUp
            String sql3 = "SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP";
            rs = st.executeQuery(sql3);
            System.out.println("Sum of product: ");
            while(rs.next()) {
                String sum = rs.getString("SUM(PRODUCT_COUNT)");
                String MONTH = rs.getString("MONTH");
                System.out.println("Sum: " + sum + ", MONTH: " + MONTH);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

输出

获得的输出如下所示 -

Product table created successfully....!
Records inserted successfully...!
Table records: 
PRODUCT_ID: 101, PRODUCT_NAME: Comb, PRODUCT_COUNT: 2345, MONTH: January
PRODUCT_ID: 102, PRODUCT_NAME: Coffee Mugs, PRODUCT_COUNT: 1242, MONTH: January
PRODUCT_ID: 103, PRODUCT_NAME: Cutlery, PRODUCT_COUNT: 124, MONTH: January
PRODUCT_ID: 101, PRODUCT_NAME: Comb, PRODUCT_COUNT: 3263, MONTH: February
Sum of product: 
Sum: 3263, MONTH: February
Sum: 3711, MONTH: January
Sum: 6974, MONTH: null

import mysql.connector
# 建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# 创建游标对象
cursorObj = connection.cursor()
# 提供汇总查询
rollup_query = """SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP"""
cursorObj.execute(rollup_query)
# 获取并打印结果
results = cursorObj.fetchall()
print("Rollup Results:")
for row in results:
    print(f"Product Count: {row[0]}, MONTH: {row[1]}")
# 关闭游标和连接
cursorObj.close()
connection.close()

输出

获得的输出如下所示 -

Rollup Results:
Product Count: 14680, MONTH: February
Product Count: 3711, MONTH: January
Product Count: 18391, MONTH: None

MySQL 8 教程

MySQL 8 数据库

MySQL 8 用户

MySQL 8 表

MySQL 8 查询

MySQL 8 视图

MySQL 8 索引

MySQL 运算符和子句

MySQL 连接

MySQL 键

MySQL 触发器

MySQL 8 数据类型

MySQL 正则表达式

MySQL 全文搜索

MySQL8 函数和运算符

MySQL 8 其他概念

MySQL 8 实用资源

MySQL - ROLLUP

MySQL ROLLUP 子句

语法

示例

输出

对多列执行 ROLLUP 操作

示例

使用客户端程序进行汇总

语法

示例

输出

输出

输出

输出

颜色选择器

读后有收获微信请站长喝咖啡

错误报告

您的建议:

感谢您的帮助！