mysql的行怎麼轉為列

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

本教學操作環境：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

先來看看轉換後來的結果：

可以看出，這裡行轉列是將原來的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中文網其他相關文章！