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;
這將返回以下結果
這就是在 MySQL 中模擬 UNPIVOT 操作的方法。通過為每個你想要“unpivot”的列創(chuàng)建一個 SELECT 語句,并使用 UNION ALL 將它們組合在一起,你可以得到期望的多行格式結果。
到此這篇關于MySQL中實現行列轉換的操作示例的文章就介紹到這了,更多相關MySQL行列轉換內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
MySQL生產庫Insert了2次同樣的記錄但是主鍵ID是不一樣的問題的分析過程
這篇文章主要介紹了MySQL生產庫Insert了2次同樣的記錄但是主鍵ID是不一樣的問題的分析過程,需要的朋友可以參考下2014-02-02