搞定mysql行轉列的7種方法以及列轉行
更新時間：2024年03月06日 09:08:28 作者：遨游在知識的海洋里無法自拔
在MySQL數(shù)據(jù)庫中,有時候我們需要將一列數(shù)據(jù)轉化為行數(shù)據(jù),以便更好地進行數(shù)據(jù)分析和處理,下面這篇文章主要給大家介紹了關于搞定mysql行轉列的7種方法以及列轉行的相關資料,需要的朋友可以參考下
一、行轉列
即將原本同一列下多行的不同內(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種方法以及列轉行的文章就介紹到這了,更多相關mysql行轉列及列轉行內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了mysql的集群模式 galera-cluster部署詳解，小編覺得挺不錯的，現(xiàn)在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2019-02-02
這篇文章主要介紹了MySQL基本命令、常用命令總結,需要的朋友可以參考下2014-03-03
對于小的偏移量，直接使用limit來查詢沒有什么問題，但隨著數(shù)據(jù)量的增大，越往后分頁，limit語句的偏移量就會越大，速度也會明顯變慢，接下來文章就向大家介紹其的原由，感興趣的小伙伴可參考下面文章具體內(nèi)容2021-09-09
navicat 連接數(shù)據(jù)庫隔段時間后自動斷開連接的解決方案
這篇文章主要介紹了navicat 連接數(shù)據(jù)庫隔段時間后自動斷開連接的解決方案，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
mysql數(shù)據(jù)庫常見的優(yōu)化操作總結（經(jīng)驗分享）
這篇文章主要給大家介紹了關于mysql數(shù)據(jù)庫常見的優(yōu)化操作，文章總結的都是個人日常開發(fā)使用mysql數(shù)據(jù)庫的經(jīng)驗所得，其中包括Index索引、少用SELECT*、EXPLAIN SELECT以及開啟查詢緩存等相關資料，相信會對大家具有一定的參考價值，需要的朋友們下面來一起看看吧。2017-04-04
本文主要介紹了詳解MySql中InnoDB存儲引擎中的各種鎖，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-02-02
本文給大家介紹了MySql數(shù)據(jù)庫查詢中的特殊命令,包括mysql的安裝，特殊查詢語句，非常不錯，具有參考借鑒價值，需要的朋友可以參考下2017-11-11
MySQL 8.0.26版本升級32版本查詢數(shù)據(jù)為空的解決方案
這篇文章主要介紹了MySQL 8.0.26版本升級32版本查詢數(shù)據(jù)為空的跟蹤,本文給大家分享兩種解決方法,結合實例代碼給大家介紹的非常詳細,需要的朋友參考下吧2024-03-03
這篇文章主要介紹了Mysql中如何刪除某個字段的最后四個字符，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-09-09
下面小編就為大家?guī)硪黄€上MYSQL同步報錯故障處理方法總結(必看篇)。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-03-03