mysql中行如何轉(zhuǎn)為列

2024-07-24 12:33:15

字體：大中小

供稿：網(wǎng)友

　　這篇“mysql的行如何轉(zhuǎn)為列”文章的知識(shí)點(diǎn)大部分人都不太理解，所以小編給大家總結(jié)了以下內(nèi)容，內(nèi)容詳細(xì)，步驟清晰，具有一定的借鑒價(jià)值，希望大家閱讀完這篇文章能有所收獲，下面我們一起來看看這篇“mysql的行如何轉(zhuǎn)為列”文章吧。

　　mysql行轉(zhuǎn)列的方法：1、利用“SUM(CASE 表名 WHEN 字段名 THEN score ELSE 0 END) as 字段名”操作轉(zhuǎn)換；2、利用“SUM(IF(表名=字段名,score,0)) as 字段名”操作轉(zhuǎn)換。

　　本教程操作環(huán)境：windows10系統(tǒng)、mysql8.0.22版本、Dell G3電腦。

　　行轉(zhuǎn)列

　　即將原本同一列下多行的不同內(nèi)容作為多個(gè)字段，輸出對(duì)應(yīng)內(nèi)容。

　　建表語(yǔ)句

　　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 '成績(jī)',
　　    PRIMARY KEY(id)
　　)ENGINE = INNODB DEFAULT CHARSET = utf8;
　　插入數(shù)據(jù)

　　INSERT INTO tb_score(userid,subject,score) VALUES ('001','語(yǔ)文',90);
　　INSERT INTO tb_score(userid,subject,score) VALUES ('001','數(shù)學(xué)',92);
　　INSERT INTO tb_score(userid,subject,score) VALUES ('001','英語(yǔ)',80);
　　INSERT INTO tb_score(userid,subject,score) VALUES ('002','語(yǔ)文',88);
　　INSERT INTO tb_score(userid,subject,score) VALUES ('002','數(shù)學(xué)',90);
　　INSERT INTO tb_score(userid,subject,score) VALUES ('002','英語(yǔ)',75.5);
　　INSERT INTO tb_score(userid,subject,score) VALUES ('003','語(yǔ)文',70);
　　INSERT INTO tb_score(userid,subject,score) VALUES ('003','數(shù)學(xué)',85);
　　INSERT INTO tb_score(userid,subject,score) VALUES ('003','英語(yǔ)',90);
　　INSERT INTO tb_score(userid,subject,score) VALUES ('003','政治',82);
　　查詢數(shù)據(jù)表中的內(nèi)容（即轉(zhuǎn)換前的結(jié)果）

　　SELECT * FROM tb_score

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

　　1、使用case...when....then 進(jìn)行行轉(zhuǎn)列

　　SELECT userid,
　　SUM(CASE `subject` WHEN '語(yǔ)文' THEN score ELSE 0 END) as '語(yǔ)文',
　　SUM(CASE `subject` WHEN '數(shù)學(xué)' THEN score ELSE 0 END) as '數(shù)學(xué)',
　　SUM(CASE `subject` WHEN '英語(yǔ)' THEN score ELSE 0 END) as '英語(yǔ)',
　　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`='語(yǔ)文',score,0)) as '語(yǔ)文',
　　SUM(IF(`subject`='數(shù)學(xué)',score,0)) as '數(shù)學(xué)',
　　SUM(IF(`subject`='英語(yǔ)',score,0)) as '英語(yǔ)',
　　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對(duì)應(yīng)的subject="語(yǔ)文"的記錄只有一條，所以SUM() 的值就等于對(duì)應(yīng)那一條記錄的score的值。

　　假如userid ='001' and subject='語(yǔ)文' 的記錄有兩條，則此時(shí)SUM() 的值將會(huì)是這兩條記錄的和，同理，使用Max()的值將會(huì)是這兩條記錄里面值最大的一個(gè)。但是正常情況下，一個(gè)user對(duì)應(yīng)一個(gè)subject只有一個(gè)分?jǐn)?shù)，因此可以使用SUM()、MAX()、MIN()、AVG()等聚合函數(shù)都可以達(dá)到行轉(zhuǎn)列的效果。

　　（2）IF(`subject`='語(yǔ)文',score,0) 作為條件，即對(duì)所有subject='語(yǔ)文'的記錄的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`='語(yǔ)文',score,0)) AS 語(yǔ)文,
　　SUM(IF(`subject`='數(shù)學(xué)',score,0)) AS 數(shù)學(xué),
　　SUM(IF(`subject`='英語(yǔ)',score,0)) AS 英語(yǔ),
　　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將匯總行標(biāo)題顯示為 Total

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

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

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

　　6、動(dòng)態(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 成績(jī) FROM tb_score
　　GROUP BY userid

　　group_concat()，手冊(cè)上說明:該函數(shù)返回帶有來自一個(gè)組的連接的非NULL值的字符串結(jié)果。

　　比較抽象，難以理解。通俗點(diǎn)理解，其實(shí)是這樣的：group_concat()會(huì)計(jì)算哪些行屬于同一組，將屬于同一組的列顯示出來。要返回哪些列，由函數(shù)參數(shù)(就是字段名)決定。分組必須有個(gè)標(biāo)準(zhǔn)，就是根據(jù)group by指定的列進(jìn)行分組。

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

（編輯：武林網(wǎng)）

上一篇：mysql引擎間的差別有哪些

下一篇：怎么了解mysql多表操作