mysql行轉列(7種方法)和列轉行的實現(xiàn)

更新時間：2026年03月18日 09:46:20 作者：yige45

本文主要介紹了mysql行轉列(7種方法)和列轉行的實現(xiàn),文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧

1、使用case…when…then 進行行轉列
2、使用IF() 進行行轉列：
3、利用SUM(IF()) 生成列 + WITH ROLLUP 生成匯總行,并利用 IFNULL將匯總行標題顯示為Total
4、利用SUM(IF()) 生成列 + UNION 生成匯總行,并利用 IFNULL將匯總行標題顯示為 Total
5、利用SUM(IF()) 生成列，直接生成結果不再利用子查詢
6、動態(tài)，適用于列不確定情況
7、合并字段顯示：利用group_concat()

二、列轉行

一、行轉列

即將原本同一列下多行的不同內(nèi)容作為多個字段，輸出對應內(nèi)容。
建表語句

DROP TABLE IF EXISTS tb_score;

CREATE TABLE tb_score(
    id INT(11) NOT NULL auto_increment,
    userid VARCHAR(20) NOT NULL COMMENT '用戶id',
    subject VARCHAR(20) COMMENT '科目',
    score DOUBLE COMMENT '成績',
    PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

插入數(shù)據(jù)

INSERT INTO tb_score(userid,subject,score) VALUES ('001','語文',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('001','數(shù)學',92);
INSERT INTO tb_score(userid,subject,score) VALUES ('001','英語',80);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','語文',88);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','數(shù)學',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','英語',75.5);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','語文',70);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','數(shù)學',85);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','英語',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','政治',82);

查詢數(shù)據(jù)表中的內(nèi)容（即轉換前的結果）

SELECT * FROM tb_score

先來看一下轉換后的結果：

可以看出，這里行轉列是將原來的subject字段的多行內(nèi)容選出來，作為結果集中的不同列，并根據(jù)userid進行分組顯示對應的score。

1、使用case…when…then 進行行轉列

SELECT userid,
SUM(CASE `subject` WHEN '語文' THEN score ELSE 0 END) as '語文',
SUM(CASE `subject` WHEN '數(shù)學' THEN score ELSE 0 END) as '數(shù)學',
SUM(CASE `subject` WHEN '英語' THEN score ELSE 0 END) as '英語',
SUM(CASE `subject` WHEN '政治' THEN score ELSE 0 END) as '政治' 
FROM tb_score 
GROUP BY userid

2、使用IF() 進行行轉列：

SELECT userid,
SUM(IF(`subject`='語文',score,0)) as '語文',
SUM(IF(`subject`='數(shù)學',score,0)) as '數(shù)學',
SUM(IF(`subject`='英語',score,0)) as '英語',
SUM(IF(`subject`='政治',score,0)) as '政治' 
FROM tb_score 
GROUP BY userid

注意點：

（1）SUM() 是為了能夠使用GROUP BY根據(jù)userid進行分組，因為每一個userid對應的subject="語文"的記錄只有一條，所以SUM() 的值就等于對應那一條記錄的score的值。

假如userid =‘001’ and subject=‘語文’ 的記錄有兩條，則此時SUM() 的值將會是這兩條記錄的和，同理，使用Max()的值將會是這兩條記錄里面值最大的一個。但是正常情況下，一個user對應一個subject只有一個分數(shù)，因此可以使用SUM()、MAX()、MIN()、AVG()等聚合函數(shù)都可以達到行轉列的效果。

（2）IF(subject=‘語文’,score,0) 作為條件，即對所有subject='語文’的記錄的score字段進行SUM()、MAX()、MIN()、AVG()操作，如果score沒有值則默認為0。

3、利用SUM(IF()) 生成列 + WITH ROLLUP 生成匯總行,并利用 IFNULL將匯總行標題顯示為Total

SELECT IFNULL(userid,'total') AS userid,
SUM(IF(`subject`='語文',score,0)) AS 語文,
SUM(IF(`subject`='數(shù)學',score,0)) AS 數(shù)學,
SUM(IF(`subject`='英語',score,0)) AS 英語,
SUM(IF(`subject`='政治',score,0)) AS 政治,
SUM(IF(`subject`='total',score,0)) AS total
FROM(
    SELECT userid,IFNULL(`subject`,'total') AS `subject`,SUM(score) AS score
    FROM tb_score
    GROUP BY userid,`subject`
    WITH ROLLUP
    HAVING userid IS NOT NULL
)AS A 
GROUP BY userid
WITH ROLLUP;

運行結果：

4、利用SUM(IF()) 生成列 + UNION 生成匯總行,并利用 IFNULL將匯總行標題顯示為 Total

SELECT userid,
SUM(IF(`subject`='語文',score,0)) AS 語文,
SUM(IF(`subject`='數(shù)學',score,0)) AS 數(shù)學,
SUM(IF(`subject`='英語',score,0)) AS 英語,
SUM(IF(`subject`='政治',score,0)) AS 政治,
SUM(score) AS TOTAL 
FROM tb_score
GROUP BY userid
UNION
SELECT 'TOTAL',SUM(IF(`subject`='語文',score,0)) AS 語文,
SUM(IF(`subject`='數(shù)學',score,0)) AS 數(shù)學,
SUM(IF(`subject`='英語',score,0)) AS 英語,
SUM(IF(`subject`='政治',score,0)) AS 政治,
SUM(score) FROM tb_score

運行結果：

5、利用SUM(IF()) 生成列，直接生成結果不再利用子查詢

SELECT IFNULL(userid,'TOTAL') AS userid,
SUM(IF(`subject`='語文',score,0)) AS 語文,
SUM(IF(`subject`='數(shù)學',score,0)) AS 數(shù)學,
SUM(IF(`subject`='英語',score,0)) AS 英語,
SUM(IF(`subject`='政治',score,0)) AS 政治,
SUM(score) AS TOTAL 
FROM tb_score
GROUP BY userid WITH ROLLUP;

運行結果：

6、動態(tài)，適用于列不確定情況

SET @EE='';
select @EE :=CONCAT(@EE,'sum(if(subject= '',subject,'',score,0)) as ',subject, ',') AS aa FROM (SELECT DISTINCT subject FROM tb_score) A ;

SET @QQ = CONCAT('select ifnull(userid,'TOTAL')as userid,',@EE,' sum(score) as TOTAL from tb_score group by userid WITH ROLLUP');
-- SELECT @QQ;

PREPARE stmt FROM @QQ;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

運行結果：

7、合并字段顯示：利用group_concat()

SELECT userid,GROUP_CONCAT(`subject`,":",score)AS 成績 FROM tb_score
GROUP BY userid

運行結果：

group_concat()，手冊上說明:該函數(shù)返回帶有來自一個組的連接的非NULL值的字符串結果。
比較抽象，難以理解。通俗點理解，其實是這樣的：group_concat()會計算哪些行屬于同一組，將屬于同一組的列顯示出來。要返回哪些列，由函數(shù)參數(shù)(就是字段名)決定。分組必須有個標準，就是根據(jù)group by指定的列進行分組。

結論：group_concat()函數(shù)可以很好的建屬于同一分組的多個行轉化為一個列。

二、列轉行

建表語句：

CREATE TABLE tb_score1(
    id INT(11) NOT NULL auto_increment,
    userid VARCHAR(20) NOT NULL COMMENT '用戶id',
    cn_score DOUBLE COMMENT '語文成績',
    math_score DOUBLE COMMENT '數(shù)學成績',
    en_score DOUBLE COMMENT '英語成績',
    po_score DOUBLE COMMENT '政治成績',
    PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

插入數(shù)據(jù)：

INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('001',90,92,80,0);
INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('002',88,90,75.5,0);
INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('003',70,85,90,82);

查詢數(shù)據(jù)表中的內(nèi)容（即轉換前的結果）

SELECT * FROM tb_score1

轉換后：

本質(zhì)是將userid的每個科目分數(shù)分散成一條記錄顯示出來。

直接上SQL：

SELECT userid,'語文' AS course,cn_score AS score FROM tb_score1
UNION ALL
SELECT userid,'數(shù)學' AS course,math_score AS score FROM tb_score1
UNION ALL
SELECT userid,'英語' AS course,en_score AS score FROM tb_score1
UNION ALL
SELECT userid,'政治' AS course,po_score AS score FROM tb_score1
ORDER BY userid

這里將每個userid對應的多個科目的成績查出來，通過UNION ALL將結果集加起來，達到上圖的效果。

到此這篇關于mysql行轉列(7種方法)和列轉行的實現(xiàn)的文章就介紹到這了,更多相關mysql行轉列和列轉行內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

MySQL數(shù)據(jù)庫升級的一些"陷阱"
這篇文章主要介紹了MySQL數(shù)據(jù)庫升級需要注意的地方，幫助大家更好的理解和學習，感興趣的朋友可以了解下
2020-08-08
Mysql主從復制與讀寫分離圖文詳解
這篇文章主要給大家介紹了關于Mysql主從復制與讀寫分離的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2021-03-03
clickhouse分布式表的操作示例詳解
這篇文章主要為大家介紹了clickhouse分布式表的操作示例詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步,早日升職加薪
2023-11-11
MYSQL單表操作學習之DDL、DML及DQL語句示例
DML、DDL、DCL和DQL是數(shù)據(jù)庫中常用的四種語言,分別用于數(shù)據(jù)操作、數(shù)據(jù)定義、數(shù)據(jù)控制和數(shù)據(jù)查詢,下面這篇文章主要給大家介紹了關于MYSQL單表操作學習之DDL、DML及DQL語句的相關資料,需要的朋友可以參考下
2024-03-03
MySQL8.x登陸root用戶突然提示mysql_native_password的實現(xiàn)
本文主要介紹了MySQL 8.x登陸root用戶突然提示mysql_native_password,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
2024-08-08
MySQL如何實現(xiàn)跨庫join查詢
這篇文章主要介紹了MySQL如何實現(xiàn)跨庫join查詢問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
2023-03-03
全面解析MySQL中的隔離級別
這篇文章主要介紹了MySQL中的隔離級別的相關資料，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下。
2021-01-01
mysql給id設置默認值為UUID的實現(xiàn)方法
由于mysql并不支持默認值為函數(shù)類型,給id設值有兩種方式,本文主要介紹了mysql給id設置默認值為UUID的實現(xiàn)方法,具有一定的參考價值,感興趣的可以了解一下
2023-08-08
mysql8.0.21安裝教程圖文詳解
這篇文章主要介紹了mysql8.0.21安裝教程，本文通過圖文并茂的形式給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下
2020-09-09
MySQL中group_concat函數(shù)深入理解
本文通過實例介紹了MySQL中的group_concat函數(shù)的使用方法，需要的朋友可以適當參考下
2012-11-11