這篇“MySQL的行如何轉為列”文章的知識點大部分人都不太理解,所以小編給大家總結了以下內容,內容詳細,步驟清晰,具有一定的借鑒價值,希望大家閱讀完這篇文章能有所收獲,下面我們一起來看看這篇“mysql的行如何轉為列”文章吧。
創(chuàng)新互聯(lián)是專業(yè)的吉隆網站建設公司,吉隆接單;提供網站制作、成都網站設計,網頁設計,網站設計,建網站,PHP網站建設等專業(yè)做網站服務;采用PHP框架,可快速的進行吉隆網站開發(fā)網頁制作和功能擴展;專業(yè)做搜索引擎喜愛的網站,專業(yè)的做網站團隊,希望更多企業(yè)前來合作!
mysql行轉列的方法:1、利用“SUM(CASE 表名 WHEN 字段名 THEN score ELSE 0 END) as 字段名”操作轉換;2、利用“SUM(IF(表名=字段名,score,0)) as 字段名”操作轉換。
本教程操作環(huán)境:windows10系統(tǒng)、mysql8.0.22版本、Dell G3電腦。
行轉列
即將原本同一列下多行的不同內容作為多個字段,輸出對應內容。
建表語句
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ù)表中的內容(即轉換前的結果)
SELECT * FROM tb_score
先來看一下轉換后的結果:
可以看出,這里行轉列是將原來的subject字段的多行內容選出來,作為結果集中的不同列,并根據(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ù)可以很好的建屬于同一分組的多個行轉化為一個列。
以上就是關于“mysql的行如何轉為列”這篇文章的內容,相信大家都有了一定的了解,希望小編分享的內容對大家有幫助,若想了解更多相關的知識內容,請關注創(chuàng)新互聯(lián)行業(yè)資訊頻道。
當前標題:mysql的行如何轉為列
文章起源:http://muchs.cn/article0/ihjjio.html
成都網站建設公司_創(chuàng)新互聯(lián),為您提供網站設計、網站維護、企業(yè)網站制作、云服務器、品牌網站制作、Google
聲明:本網站發(fā)布的內容(圖片、視頻和文字)以用戶投稿、用戶轉載內容為主,如果涉及侵權請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內容未經允許不得轉載,或轉載時需注明來源: 創(chuàng)新互聯(lián)