MySQL中實現行列轉換的操作示例

更新時間：2024年06月18日 11:28:16 作者：zxrhhm

在 MySQL 中進行行列轉換（即,將某些列轉換為行或將某些行轉換為列）通常涉及使用條件邏輯和聚合函數,本文給大家介紹了MySQL中實現行列轉換的操作示例,文中有詳細的代碼示例供大家參考,需要的朋友可以參考下

在 MySQL 中進行行列轉換（即，將某些列轉換為行或將某些行轉換為列）通常涉及使用條件邏輯和聚合函數。雖然 MySQL 沒有像 Oracle/SQL Server 中的 PIVOT 和 UNPIVOT 那樣的直接功能，但你可以通過結合 CASE 語句、UNION 或 UNION ALL、以及 GROUP BY 等來實現這些轉換。

1、行轉列的操作

在 MySQL 中，并沒有內置的 PIVOT 函數，如 Oracle/SQL Server 中那樣。但是，你可以使用條件聚合或 CASE 語句來模擬 PIVOT 操作。

以下是一個簡單的示例，說明如何在 MySQL 中模擬 PIVOT。

假設你有一個名為 t_sales 的表，它記錄了銷售數據，結構如下：

CREATE TABLE t_sales (  
    id int primary key auto_increment,
    col_year INT,  
    product VARCHAR(255),  
    amount DECIMAL(10, 2)  
);  
  
INSERT INTO t_sales (col_year, product, amount) VALUES  
(2020, 'A001', 100),  
(2020, 'B001', 120),  
(2021, 'A001', 150),  
(2021, 'B001', 150),
(2022, 'A001', 260),  
(2022, 'B001', 240),
(2023, 'B001', 330),
(2024, 'A001', 440);

(root@localhost:mysql.sock)[superdb 10:49:26]>select * from t_sales;
+----+----------+---------+--------+
| id | col_year | product | amount |
+----+----------+---------+--------+
|  1 |     2020 | A001    | 100.00 |
|  2 |     2020 | B001    | 120.00 |
|  3 |     2021 | A001    | 150.00 |
|  4 |     2021 | B001    | 150.00 |
|  5 |     2022 | A001    | 260.00 |
|  6 |     2022 | B001    | 240.00 |
|  7 |     2023 | B001    | 330.00 |
|  8 |     2024 | A001    | 440.00 |
+----+----------+---------+--------+
8 rows in set (0.00 sec)

現在，假設你想要將產品列 (product) 轉換為列標題，并為每個年份和產品顯示銷售額。在 Oracle/SQL Server 中，你可以使用 PIVOT 來實現這一點。但在 MySQL 中，你可以這樣做：

SELECT   
    col_year,  
    SUM(CASE WHEN product = 'A001' THEN amount ELSE 0 END) AS 'A_product',  
    SUM(CASE WHEN product = 'B001' THEN amount ELSE 0 END) AS 'B_product'  
FROM t_sales
GROUP BY col_year;

(root@localhost:mysql.sock)[superdb 10:50:29]>SELECT   
    ->     col_year,  
    ->     SUM(CASE WHEN product = 'A001' THEN amount ELSE 0 END) AS 'A_product',  
    ->     SUM(CASE WHEN product = 'B001' THEN amount ELSE 0 END) AS 'B_product'  
    -> FROM t_sales
    -> GROUP BY col_year;
    
-- 這將返回以下結果
+----------+-----------+-----------+
| col_year | A_product | B_product |
+----------+-----------+-----------+
|     2020 |    100.00 |    120.00 |
|     2021 |    150.00 |    150.00 |
|     2022 |    260.00 |    240.00 |
|     2023 |      0.00 |    330.00 |
|     2024 |    440.00 |      0.00 |
+----------+-----------+-----------+
5 rows in set (0.00 sec)

這就是在 MySQL 中模擬 PIVOT 的方法。對于更復雜的轉換或更多的產品，你可能需要擴展 CASE 語句來包含更多的條件。

2、列轉行的操作

在 MySQL 中，沒有直接的 UNPIVOT 操作，因為 UNPIVOT 是 SQL Server 和 Oracle 等數據庫系統(tǒng)中的功能，用于將多列轉換為多行。但是，你可以使用 MySQL 的查詢技巧來模擬 UNPIVOT 操作。

假設你有一個類似 PIVOT 后的結果集，并且你想要將其轉換回原始的多行格式，你可以使用 UNION ALL 或 UNION（取決于是否要消除重復行）來模擬 UNPIVOT。

以下是一個示例，說明如何在 MySQL 中模擬 UNPIVOT 操作：

假設你有一個 t_pivoted_sales 表，它是通過 PIVOT（或上述的 MySQL 模擬方法）得到的：

CREATE TABLE t_pivoted_sales (  
    id int primary key auto_increment,
    col_year INT,  
    A_product DECIMAL(18, 2),  
    B_product DECIMAL(18, 2)  
);  
  
INSERT INTO t_pivoted_sales (col_year, A_product, B_product) VALUES  
(2020, 100.00, 120.0),  
(2021, 150.00, 150.00),
(2022, 260.00, 240.00),
(2023, 0.00, 330.00),
(2024, 440.00, 0.00);

(root@localhost:mysql.sock)[superdb 11:02:54]>select * from t_pivoted_sales;
+----+----------+-----------+-----------+
| id | col_year | A_product | B_product |
+----+----------+-----------+-----------+
|  1 |     2020 |    100.00 |    120.00 |
|  2 |     2021 |    150.00 |    150.00 |
|  3 |     2022 |    260.00 |    240.00 |
|  4 |     2023 |      0.00 |    330.00 |
|  5 |     2024 |    440.00 |      0.00 |
+----+----------+-----------+-----------+
5 rows in set (0.00 sec)

現在，你想要將 A_product 和 B_product 列轉換回多行格式，其中有一個額外的列product來表示產品（A_product 或 B_product）。你可以使用以下查詢來模擬 UNPIVOT：

SELECT col_year, 'A_product' AS product, A_product AS amount FROM t_pivoted_sales
union all
SELECT col_year, 'B_product' AS product, B_product AS amount FROM t_pivoted_sales
order by col_year;

這將返回以下結果