mysql的行怎么轉為列

mysql行轉列的方法:1、利用“SUM(CASE 表名 WHEN 字段名 THEN score ELSE 0 END) as 字段名”操作轉換;2、利用“SUM(if(表名=字段名,score,0)) as 字段名”操作轉換。

mysql的行怎么轉為列

本教程操作環境:windows10系統、mysql8.0.22版本、Dell G3電腦。

mysql的行怎么轉為列

行轉列

即將原本同一列下多行的不同內容作為多個字段,輸出對應內容。

建表語句

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

先來看一下轉換后的結果:

mysql的行怎么轉為列

可以看出,這里行轉列是將原來的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;

運行結果:

mysql的行怎么轉為列

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

運行結果:

mysql的行怎么轉為列

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;

運行結果:

mysql的行怎么轉為列

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;

運行結果:

mysql的行怎么轉為列

7、合并字段顯示:利用group_concat()

SELECT?userid,GROUP_CONCAT(`subject`,":",score)AS?成績?FROM?tb_score GROUP?BY?userid

運行結果:

mysql的行怎么轉為列

group_concat(),手冊上說明:該函數返回帶有來自一個組的連接的非NULL值的字符串結果。

比較抽象,難以理解。通俗點理解,其實是這樣的:group_concat()會計算哪些行屬于同一組,將屬于同一組的列顯示出來。要返回哪些列,由函數參數(就是字段名)決定。分組必須有個標準,就是根據group?by指定的列進行分組。

結論:group_concat()函數可以很好的建屬于同一分組的多個行轉化為一個列。

推薦學習:mysql視頻教程

以上就是

? 版權聲明
THE END
喜歡就支持一下吧
點贊13 分享