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

使用客戶端程式進行 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://: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
列印頁面
上一篇
下一篇
廣告