搞定mysql行轉(zhuǎn)列的7種方法以及列轉(zhuǎn)行
更新時間：2024年03月06日 09:08:28 作者：遨游在知識的海洋里無法自拔
在MySQL數(shù)據(jù)庫中,有時候我們需要將一列數(shù)據(jù)轉(zhuǎn)化為行數(shù)據(jù),以便更好地進行數(shù)據(jù)分析和處理,下面這篇文章主要給大家介紹了關(guān)于搞定mysql行轉(zhuǎn)列的7種方法以及列轉(zhuǎn)行的相關(guān)資料,需要的朋友可以參考下
一、行轉(zhuǎn)列
即將原本同一列下多行的不同內(nèi)容作為多個字段，輸出對應(yīng)內(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ù)學(xué)',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ù)學(xué)',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ù)學(xué)',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)容（即轉(zhuǎn)換前的結(jié)果）
SELECT * FROM tb_score
先來看一下轉(zhuǎn)換后的結(jié)果：
可以看出，這里行轉(zhuǎn)列是將原來的subject字段的多行內(nèi)容選出來，作為結(jié)果集中的不同列，并根據(jù)userid進行分組顯示對應(yīng)的score。
1、使用case…when…then 進行行轉(zhuǎn)列
SELECT userid, SUM(CASE `subject` WHEN '語文' THEN score ELSE 0 END) as '語文', SUM(CASE `subject` WHEN '數(shù)學(xué)' THEN score ELSE 0 END) as '數(shù)學(xué)', 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() 進行行轉(zhuǎn)列：
SELECT userid, SUM(IF(`subject`='語文',score,0)) as '語文', SUM(IF(`subject`='數(shù)學(xué)',score,0)) as '數(shù)學(xué)', 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對應(yīng)的subject="語文"的記錄只有一條，所以SUM() 的值就等于對應(yīng)那一條記錄的score的值。
假如userid =‘001’ and subject=‘語文’ 的記錄有兩條，則此時SUM() 的值將會是這兩條記錄的和，同理，使用Max()的值將會是這兩條記錄里面值最大的一個。但是正常情況下，一個user對應(yīng)一個subject只有一個分數(shù)，因此可以使用SUM()、MAX()、MIN()、AVG()等聚合函數(shù)都可以達到行轉(zhuǎn)列的效果。
（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ù)學(xué)',score,0)) AS 數(shù)學(xué),
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;運行結(jié)果：
4、利用SUM(IF()) 生成列 + UNION 生成匯總行,并利用 IFNULL將匯總行標題顯示為 Total
SELECT userid, SUM(IF(`subject`='語文',score,0)) AS 語文, SUM(IF(`subject`='數(shù)學(xué)',score,0)) AS 數(shù)學(xué), 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ù)學(xué)',score,0)) AS 數(shù)學(xué), SUM(IF(`subject`='英語',score,0)) AS 英語, SUM(IF(`subject`='政治',score,0)) AS 政治, SUM(score) FROM tb_score
運行結(jié)果：
5、利用SUM(IF()) 生成列，直接生成結(jié)果不再利用子查詢
SELECT IFNULL(userid,'TOTAL') AS userid, SUM(IF(`subject`='語文',score,0)) AS 語文, SUM(IF(`subject`='數(shù)學(xué)',score,0)) AS 數(shù)學(xué), SUM(IF(`subject`='英語',score,0)) AS 英語, SUM(IF(`subject`='政治',score,0)) AS 政治, SUM(score) AS TOTAL FROM tb_score GROUP BY userid WITH ROLLUP;
運行結(jié)果：
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;運行結(jié)果：
7、合并字段顯示：利用group_concat()
SELECT userid,GROUP_CONCAT(`subject`,":",score)AS 成績 FROM tb_score GROUP BY userid
運行結(jié)果：
group_concat()，手冊上說明:該函數(shù)返回帶有來自一個組的連接的非NULL值的字符串結(jié)果。
比較抽象，難以理解。通俗點理解，其實是這樣的：group_concat()會計算哪些行屬于同一組，將屬于同一組的列顯示出來。要返回哪些列，由函數(shù)參數(shù)(就是字段名)決定。分組必須有個標準，就是根據(jù)group by指定的列進行分組。
結(jié)論：group_concat()函數(shù)可以很好的建屬于同一分組的多個行轉(zhuǎn)化為一個列。
二、列轉(zhuǎn)行
建表語句：
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ù)學(xué)成績',
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)容（即轉(zhuǎn)換前的結(jié)果）
SELECT * FROM tb_score1
轉(zhuǎn)換后：
本質(zhì)是將userid的每個科目分數(shù)分散成一條記錄顯示出來。
直接上SQL：
SELECT userid,'語文' AS course,cn_score AS score FROM tb_score1 UNION ALL SELECT userid,'數(shù)學(xué)' 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對應(yīng)的多個科目的成績查出來，通過UNION ALL將結(jié)果集加起來，達到上圖的效果。
總結(jié)
到此這篇關(guān)于搞定mysql行轉(zhuǎn)列的7種方法以及列轉(zhuǎn)行的文章就介紹到這了,更多相關(guān)mysql行轉(zhuǎn)列及列轉(zhuǎn)行內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
本篇文章是對關(guān)于php操作mysql執(zhí)行數(shù)據(jù)庫查詢的一些常用操作進行了詳細的匯總介紹,非常的細致全面，也很簡單，需要的朋友參考下2015-04-04
MySQL中DATE_FORMATE函數(shù)使用時的注意點
這篇文章主要介紹了MySQL中DATE_FORMATE函數(shù)使用時的注意點,主要是針對其內(nèi)置的字符集使用時需要轉(zhuǎn)換而進行說明,需要的朋友可以參考下2015-05-05
MySQL數(shù)據(jù)庫IP白名單的安全設(shè)置指南
本文詳細指導(dǎo)如何在MySQL服務(wù)器上安全地設(shè)置IP白名單,包括登錄、查看權(quán)限、使用GRANT語句、刷新權(quán)限以及防火墻和云服務(wù)注意事項,確保數(shù)據(jù)庫安全,防止未經(jīng)授權(quán)訪問,需要的朋友可以參考下2025-08-08
MySQL是當前最為常用的關(guān)系型數(shù)據(jù)庫之一,它的出色性能和可靠性使得它成為了許多互聯(lián)網(wǎng)企業(yè)的首選,但是,如何讓MySQL支撐億級流量的挑戰(zhàn),成為了很多技術(shù)人員需要面對和解決的問題,需要的朋友可以參考下2023-10-10
這篇文章主要介紹了SQL之各種join小結(jié)詳細講解,本篇文章通過簡要的案例,講解了該項技術(shù)的了解與使用,以下就是詳細內(nèi)容,需要的朋友可以參考下2021-08-08
MySQL系列之五 視圖、存儲函數(shù)、存儲過程、觸發(fā)器
視圖就是一條select語句執(zhí)行后返回的結(jié)果集;觸發(fā)器是與表有關(guān)的數(shù)據(jù)庫對象,在滿足定義條件時觸發(fā),并執(zhí)行觸發(fā)器中定義的語句集合;函數(shù)存儲著一系列sql語句,調(diào)用函數(shù)就是一次性執(zhí)行這些語句,而存儲過程就是一組可編程的函數(shù),需要的朋友可以參考下2021-07-07
之前遇到一個問題,就是在將項目遷移到別的服務(wù)器的時候出現(xiàn)圖片路徑不對的問題,因為存放圖片的方式是在數(shù)據(jù)庫中存放圖片路徑,但是路徑前卻帶了域名,所以本文將給大家介紹在MySQL中存儲圖片的操作步驟,需要的朋友可以參考下2024-04-04
這篇文章主要介紹了Centos7 下Mysql5.7.19安裝教程詳解,小編認為非常不錯，特此分享到腳本之家平臺，需要的朋友參考下吧2017-09-09
詳解MySQL如何實現(xiàn)數(shù)據(jù)批量更新
最近需要批量更新大量數(shù)據(jù),習(xí)慣了寫sql,所以還是用sql來實現(xiàn),下面這篇文章主要給大家總結(jié)介紹了關(guān)于MySQL批量更新的方式,需要的朋友可以參考下2023-10-10
這篇文章主要為大家詳細介紹了mysql出現(xiàn)提示錯誤10061的解決方法，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-10-10