MySQL行列轉(zhuǎn)換常見的操作方法詳解
更新時(shí)間：2025年10月29日 11:40:16 作者：羚風(fēng)雯
MySQL支持行轉(zhuǎn)列與列轉(zhuǎn)行操作,滿足數(shù)據(jù)分析需求,這篇文章主要介紹了MySQL行列轉(zhuǎn)換常見的操作方法,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下
前言
行列轉(zhuǎn)換是SQL中常見的操作，主要包括兩種類型：行轉(zhuǎn)列（PIVOT）和列轉(zhuǎn)行（UNPIVOT）。MySQL雖然沒有直接的PIVOT/UNPIVOT語法，但可以通過條件聚合和UNION ALL等方式實(shí)現(xiàn)。
一、行轉(zhuǎn)列（PIVOT）
行轉(zhuǎn)列是將行數(shù)據(jù)轉(zhuǎn)換為列數(shù)據(jù)，通常用于將分類數(shù)據(jù)作為列名展示。
示例場(chǎng)景：學(xué)生成績(jī)表行轉(zhuǎn)列
1. 創(chuàng)建原始表
CREATE TABLE student_scores (
student_id INT,
student_name VARCHAR(50),
subject VARCHAR(50),
score INT
);
INSERT INTO student_scores VALUES
(1, '張三', '數(shù)學(xué)', 90),
(1, '張三', '語文', 85),
(1, '張三', '英語', 92),
(2, '李四', '數(shù)學(xué)', 88),
(2, '李四', '語文', 90),
(2, '李四', '英語', 87),
(3, '王五', '數(shù)學(xué)', 95),
(3, '王五', '語文', 78),
(3, '王五', '英語', 85);2. 行轉(zhuǎn)列實(shí)現(xiàn)方法
使用條件聚合函數(shù)：
SELECT
student_id,
student_name,
MAX(CASE WHEN subject = '數(shù)學(xué)' THEN score ELSE NULL END) AS '數(shù)學(xué)',
MAX(CASE WHEN subject = '語文' THEN score ELSE NULL END) AS '語文',
MAX(CASE WHEN subject = '英語' THEN score ELSE NULL END) AS '英語'
FROM student_scores
GROUP BY student_id, student_name;結(jié)果：
+------------+--------------+--------+--------+--------+ | student_id | student_name | 數(shù)學(xué) | 語文 | 英語 | +------------+--------------+--------+--------+--------+ | 1 | 張三 | 90 | 85 | 92 | | 2 | 李四 | 88 | 90 | 87 | | 3 | 王五 | 95 | 78 | 85 | +------------+--------------+--------+--------+--------+
二、列轉(zhuǎn)行（UNPIVOT）
列轉(zhuǎn)行是將列數(shù)據(jù)轉(zhuǎn)換為行數(shù)據(jù)，通常用于將多列數(shù)據(jù)轉(zhuǎn)換為鍵值對(duì)形式。
示例場(chǎng)景：銷售數(shù)據(jù)列轉(zhuǎn)行
1. 創(chuàng)建原始表
CREATE TABLE sales_data (
id INT,
product_name VARCHAR(50),
q1_sales INT,
q2_sales INT,
q3_sales INT,
q4_sales INT
);
INSERT INTO sales_data VALUES
(1, '產(chǎn)品A', 120, 150, 180, 200),
(2, '產(chǎn)品B', 90, 110, 130, 150),
(3, '產(chǎn)品C', 200, 220, 240, 260);2. 列轉(zhuǎn)行實(shí)現(xiàn)方法
使用UNION ALL：
SELECT
id,
product_name,
'Q1' AS quarter,
q1_sales AS sales
FROM sales_data
UNION ALL
SELECT
id,
product_name,
'Q2' AS quarter,
q2_sales AS sales
FROM sales_data
UNION ALL
SELECT
id,
product_name,
'Q3' AS quarter,
q3_sales AS sales
FROM sales_data
UNION ALL
SELECT
id,
product_name,
'Q4' AS quarter,
q4_sales AS sales
FROM sales_data
ORDER BY id, quarter;結(jié)果：
+----+--------------+---------+-------+ | id | product_name | quarter | sales | +----+--------------+---------+-------+ | 1 | 產(chǎn)品A | Q1 | 120 | | 1 | 產(chǎn)品A | Q2 | 150 | | 1 | 產(chǎn)品A | Q3 | 180 | | 1 | 產(chǎn)品A | Q4 | 200 | | 2 | 產(chǎn)品B | Q1 | 90 | | 2 | 產(chǎn)品B | Q2 | 110 | | 2 | 產(chǎn)品B | Q3 | 130 | | 2 | 產(chǎn)品B | Q4 | 150 | | 3 | 產(chǎn)品C | Q1 | 200 | | 3 | 產(chǎn)品C | Q2 | 220 | | 3 | 產(chǎn)品C | Q3 | 240 | | 3 | 產(chǎn)品C | Q4 | 260 | +----+--------------+---------+-------+
三、動(dòng)態(tài)行列轉(zhuǎn)換
當(dāng)列名不確定或經(jīng)常變化時(shí)，可以使用動(dòng)態(tài)SQL實(shí)現(xiàn)行列轉(zhuǎn)換。
-- 創(chuàng)建存儲(chǔ)過程實(shí)現(xiàn)動(dòng)態(tài)行轉(zhuǎn)列
DELIMITER //
CREATE PROCEDURE dynamic_pivot()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE col_name VARCHAR(50);
DECLARE col_list TEXT DEFAULT '';
DECLARE cur CURSOR FOR
SELECT DISTINCT subject FROM student_scores;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO col_name;
IF done THEN
LEAVE read_loop;
END IF;
SET col_list = CONCAT(col_list,
IF(col_list = '', '', ', '),
'MAX(CASE WHEN subject = ''', col_name, ''' THEN score ELSE NULL END) AS ''', col_name, '''');
END LOOP;
CLOSE cur;
SET @sql = CONCAT('SELECT student_id, student_name, ', col_list, ' FROM student_scores GROUP BY student_id, student_name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
-- 調(diào)用存儲(chǔ)過程
CALL dynamic_pivot();四、性能優(yōu)化建議
- 對(duì)于大型數(shù)據(jù)集的列轉(zhuǎn)行，考慮使用臨時(shí)表替代UNION ALL
- 在行轉(zhuǎn)列時(shí)，確保GROUP BY子句包含所有非聚合列
- 為常用轉(zhuǎn)換創(chuàng)建視圖，提高查詢效率
- 在動(dòng)態(tài)SQL中，注意防止SQL注入
五、實(shí)際應(yīng)用場(chǎng)景
- 報(bào)表生成：將行數(shù)據(jù)轉(zhuǎn)換為適合報(bào)表展示的列格式
- 數(shù)據(jù)透視：分析不同維度的數(shù)據(jù)關(guān)系
- ETL過程：數(shù)據(jù)清洗和轉(zhuǎn)換
- 數(shù)據(jù)展示：將數(shù)據(jù)庫格式轉(zhuǎn)換為前端需要的格式
到此這篇關(guān)于MySQL行列轉(zhuǎn)換常見操作方法的文章就介紹到這了,更多相關(guān)MySQL行列轉(zhuǎn)換內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Mysql LONGTEXT 類型存儲(chǔ)大文件(二進(jìn)制也可以) (修改+調(diào)試+整理)
MySql2.cpp : Defines the entry point for the console application.2009-07-07
當(dāng)涉及到大量數(shù)據(jù)時(shí),對(duì)于?ORDER?BY?操作,可以考慮為相應(yīng)的列添加索引,如果不使用索引,mysql會(huì)使用filesort來進(jìn)行排序,這篇文章主要介紹了mysql?order?by?排序原理,需要的朋友可以參考下2024-02-02
這篇文章主要介紹了MySql Select ，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-03-03
在默認(rèn)設(shè)置下,MySQL使用異步復(fù)制,主庫發(fā)送binlog后不等待從庫確認(rèn),可能導(dǎo)致數(shù)據(jù)不一致或丟失,半同步復(fù)制則在主庫更新數(shù)據(jù)后先等待從庫確認(rèn)同步完成,本文給大家介紹MYSQL半同步配置,感興趣的朋友一起看看吧2023-09-09
MYSQL單表操作學(xué)習(xí)之DDL、DML及DQL語句示例
DML、DDL、DCL和DQL是數(shù)據(jù)庫中常用的四種語言,分別用于數(shù)據(jù)操作、數(shù)據(jù)定義、數(shù)據(jù)控制和數(shù)據(jù)查詢,下面這篇文章主要給大家介紹了關(guān)于MYSQL單表操作學(xué)習(xí)之DDL、DML及DQL語句的相關(guān)資料,需要的朋友可以參考下2024-03-03
數(shù)據(jù)庫函數(shù)是一種具有某種功能的模塊，可以接收零個(gè)或多個(gè)輸入值，并且返回一個(gè)輸出值，這篇文章給大家介紹MySQL 十大常用字符串函數(shù)，感興趣的朋友跟隨小編一起看看吧2021-06-06
這篇文章主要給大家介紹了MySQL數(shù)據(jù)庫忽略大小寫的配置方法,文中通過代碼示例給大家講解的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作有一定的幫助,需要的朋友可以參考下2024-01-01
select?語句的作用是根據(jù)輸入的條件返回指定的數(shù)據(jù)結(jié)果，select?的語法可以有很多種查詢的組合，基本上能夠滿足我們所有的查詢數(shù)據(jù)需求，這篇文章主要介紹了Mysql?查詢患某種疾病的患者,需要的朋友可以參考下2022-10-10
本文主要介紹了mysql中token的分頁升級(jí),文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-10-10
MySQL之同表一個(gè)字段如何賦值給另一個(gè)字段
這篇文章主要介紹了MySQL之同表一個(gè)字段如何賦值給另一個(gè)字段問題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-07-07