mysql行轉列的方法:1、利用「SUM(CASE 表名WHEN 字段名THEN score ELSE 0 END) as 字段名」操作轉換;2、利用「SUM(IF(表名=字段名,score,0)) as 字段名”操作轉換。
本教學操作環境:windows10系統、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;
插入資料
INSERT INTO tb_score(userid,subject,score) VALUES ('001','语文',90); INSERT INTO tb_score(userid,subject,score) VALUES ('001','数学',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','数学',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','数学',85); INSERT INTO tb_score(userid,subject,score) VALUES ('003','英语',90); INSERT INTO tb_score(userid,subject,score) VALUES ('003','政治',82);
查詢資料表中的內容(即轉換前的結果)
SELECT * FROM tb_score
先來看看轉換後來的結果:
可以看出,這裡行轉列是將原來的subject欄位的多行內容選出來,作為結果集中的不同列,並根據userid進行分組顯示對應的score。
1、使用case...when....then 進行行轉列
SELECT userid, SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) as '语文', SUM(CASE `subject` WHEN '数学' THEN score ELSE 0 END) as '数学', 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`='数学',score,0)) as '数学', SUM(IF(`subject`='英语',score,0)) as '英语', SUM(IF(`subject`='政治',score,0)) as '政治' FROM tb_score GROUP BY userid
注意點:
(1)SUM() 是為了能夠使用GROUP BY根據userid分組,因為每一個userid對應的subject="語文"的記錄只有一條,所以SUM() 的值等於對應那一筆記錄的score的值。
假如userid ='001' and subject='語文' 的記錄有兩條,則此時SUM() 的值將會是這兩條記錄的和,同理,使用Max()的值將會是這兩筆記錄裡面值最大的一個。但是正常情況下,一個user對應一個subject只有一個分數,因此可以使用SUM()、MAX()、MIN()、AVG()等聚合函數都可以達到行轉列的效果。
(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`='数学',score,0)) AS 数学, 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`='数学',score,0)) AS 数学, 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`='数学',score,0)) AS 数学, 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`='数学',score,0)) AS 数学, 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、動態,適用於列不確定情況
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(),手冊上說明:該函數傳回帶有來自一個群組的連接的非NULL值的字串結果。
比較抽象,難以理解。通俗點理解,其實是這樣的:group_concat()會計算哪些行屬於同一組,將屬於同一組的列顯示出來。要傳回哪些列,由函數參數(就是字段名)決定。分組必須有個標準,就是依照group by指定的欄位進行分組。
結論:group_concat()函數可以很好的建屬於同一分組的多個行轉換為一個列。
推薦學習:mysql影片教學
#以上是mysql的行怎麼轉為列的詳細內容。更多資訊請關注PHP中文網其他相關文章!