MySQL - ROLLUP

Table of content
上一页
下一页

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 梳子 2345 一月
102 咖啡杯 1242 一月
103 餐具 124 一月
101 梳子 3263 二月
102 咖啡杯 10982 二月
103 餐具 435 二月

现在，让我们使用 ROLLUP 查找每个月份生产的产品总和，如下所示：

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

输出

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

SUM(PRODUCT_COUNT) MONTH
14680 二月
3711 一月
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 梳子 2
102 2 咖啡杯 2
103 2 餐具 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 梳子 2
101 2 NULL 2
102 2 咖啡杯 2
102 2 NULL 2
103 2 餐具 2
103 2 NULL 2
NULL 6 NULL 6

Learn MySQL in-depth with real-world projects through our MySQL certification course. Enroll and become a certified expert to boost your career.

使用客户端程序进行 Rollup

我们也可以使用客户端程序执行 rollup。

语法

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...!\n");
}
$sql = "INSERT INTO PRODUCT VALUES(102, 'Coffee Mugs', 1242, 'January')";
if($mysqli->query($sql)){
    printf("Second record inserted successfully...!\n");
}
$sql = "INSERT INTO PRODUCT VALUES(103, 'Cutlery', 124, 'January')";
if($mysqli->query($sql)){
    printf("Third record inserted successfully...!\n");
}
$sql = "INSERT INTO PRODUCT VALUES(101, 'Comb', 3263, 'February')";
if($mysqli->query($sql)){
    printf("Fourth record inserted successfully...!\n");
}
//display the table records
$sql = "SELECT * FROM PRODUCT";
if($result = $mysqli->query($sql)){
    printf("Table records: \n");
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("\n");
}}
//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: \n");
    while($row = mysqli_fetch_array($result)){
        printf("Sum of product: %d, MONTH: %s", $row['SUM(PRODUCT_COUNT)'], $row['MONTH']);
        printf("\n");
    }
}
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://127.0.0.1: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
# Establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object
cursorObj = connection.cursor()
# providing rollup query
rollup_query = """SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP"""
cursorObj.execute(rollup_query)
# Fetching and printing the results
results = cursorObj.fetchall()
print("Rollup Results:")
for row in results:
    print(f"Product Count: {row[0]}, MONTH: {row[1]}")
# Closing the cursor and connection
cursorObj.close()
connection.close()

输出

获得的输出如下所示：

Rollup Results:
Product Count: 14680, MONTH: February
Product Count: 3711, MONTH: January
Product Count: 18391, MONTH: None
打印页面
上一篇
下一篇
广告