mysql行轉(zhuǎn)列(7種方法)和列轉(zhuǎn)行的實(shí)現(xiàn)
一、行轉(zhuǎn)列
即將原本同一列下多行的不同內(nèi)容作為多個(gè)字段，輸出對應(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進(jìn)行分組顯示對應(yīng)的score。
1、使用case…when…then 進(jìn)行行轉(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() 進(jìn)行行轉(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
注意點(diǎn)：
（1）SUM() 是為了能夠使用GROUP BY根據(jù)userid進(jìn)行分組，因?yàn)槊恳粋€(gè)userid對應(yīng)的subject="語文"的記錄只有一條，所以SUM() 的值就等于對應(yīng)那一條記錄的score的值。
假如userid =‘001’ and subject=‘語文’ 的記錄有兩條，則此時(shí)SUM() 的值將會是這兩條記錄的和，同理，使用Max()的值將會是這兩條記錄里面值最大的一個(gè)。但是正常情況下，一個(gè)user對應(yīng)一個(gè)subject只有一個(gè)分?jǐn)?shù)，因此可以使用SUM()、MAX()、MIN()、AVG()等聚合函數(shù)都可以達(dá)到行轉(zhuǎn)列的效果。
（2）IF(subject=‘語文’,score,0) 作為條件，即對所有subject='語文’的記錄的score字段進(jìn)行SUM()、MAX()、MIN()、AVG()操作，如果score沒有值則默認(rèn)為0。
3、利用SUM(IF()) 生成列 + WITH ROLLUP 生成匯總行,并利用 IFNULL將匯總行標(biāo)題顯示為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;
運(yùn)行結(jié)果：
4、利用SUM(IF()) 生成列 + UNION 生成匯總行,并利用 IFNULL將匯總行標(biāo)題顯示為 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
運(yùn)行結(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;
運(yùn)行結(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;
運(yùn)行結(jié)果：
7、合并字段顯示：利用group_concat()
SELECT userid,GROUP_CONCAT(`subject`,":",score)AS 成績 FROM tb_score GROUP BY userid
運(yùn)行結(jié)果：
group_concat()，手冊上說明:該函數(shù)返回帶有來自一個(gè)組的連接的非NULL值的字符串結(jié)果。
比較抽象，難以理解。通俗點(diǎn)理解，其實(shí)是這樣的：group_concat()會計(jì)算哪些行屬于同一組，將屬于同一組的列顯示出來。要返回哪些列，由函數(shù)參數(shù)(就是字段名)決定。分組必須有個(gè)標(biāo)準(zhǔn)，就是根據(jù)group by指定的列進(jìn)行分組。
結(jié)論：group_concat()函數(shù)可以很好的建屬于同一分組的多個(gè)行轉(zhuǎn)化為一個(gè)列。
二、列轉(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的每個(gè)科目分?jǐn)?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
這里將每個(gè)userid對應(yīng)的多個(gè)科目的成績查出來，通過UNION ALL將結(jié)果集加起來，達(dá)到上圖的效果。
到此這篇關(guān)于mysql行轉(zhuǎn)列(7種方法)和列轉(zhuǎn)行的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)mysql行轉(zhuǎn)列和列轉(zhuǎn)行內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了MySQL數(shù)據(jù)庫升級需要注意的地方，幫助大家更好的理解和學(xué)習(xí)，感興趣的朋友可以了解下2020-08-08
這篇文章主要給大家介紹了關(guān)于Mysql主從復(fù)制與讀寫分離的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-03-03
這篇文章主要為大家介紹了clickhouse分布式表的操作示例詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2023-11-11
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
MySQL8.x登陸root用戶突然提示mysql_native_password的實(shí)現(xiàn)
本文主要介紹了MySQL 8.x登陸root用戶突然提示mysql_native_password,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-08-08
這篇文章主要介紹了MySQL如何實(shí)現(xiàn)跨庫join查詢問題，具有很好的參考價(jià)值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-03-03
這篇文章主要介紹了MySQL中的隔離級別的相關(guān)資料，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下。2021-01-01
mysql給id設(shè)置默認(rèn)值為UUID的實(shí)現(xiàn)方法
由于mysql并不支持默認(rèn)值為函數(shù)類型,給id設(shè)值有兩種方式,本文主要介紹了mysql給id設(shè)置默認(rèn)值為UUID的實(shí)現(xiàn)方法,具有一定的參考價(jià)值,感興趣的可以了解一下2023-08-08
這篇文章主要介紹了mysql8.0.21安裝教程，本文通過圖文并茂的形式給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2020-09-09
MySQL中g(shù)roup_concat函數(shù)深入理解
本文通過實(shí)例介紹了MySQL中的group_concat函數(shù)的使用方法，需要的朋友可以適當(dāng)參考下2012-11-11