MySQL行轉列的方法是什么

本篇內容介紹了“MySQL行轉列的方法是什么”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

首先，我們看一下咱們的測試表數據和預期查詢的結果：

mysql> SELECT * FROM t_gaokao_score;
+----+--------------+--------------+-------+
| id | student_name | subject      | score |
+----+--------------+--------------+-------+
|  1 | 林磊兒       | 語文         |   148 |
|  2 | 林磊兒       | 數學         |   150 |
|  3 | 林磊兒       | 英語         |   147 |
|  4 | 喬英子       | 語文         |   121 |
|  5 | 喬英子       | 數學         |   106 |
|  6 | 喬英子       | 英語         |   146 |
|  7 | 方一凡       | 語文         |    70 |
|  8 | 方一凡       | 數學         |    90 |
|  9 | 方一凡       | 英語         |    59 |
| 10 | 方一凡       | 特長加分     |   200 |
| 11 | 陳哈哈       | 語文         |   109 |
| 12 | 陳哈哈       | 數學         |    92 |
| 13 | 陳哈哈       | 英語         |    80 |
+----+--------------+--------------+-------+
13 rows in set (0.00 sec)

看看我們行轉列轉完后的結果:

+--------------+--------+--------+--------+--------------+
| student_name | 語文   | 數學   | 英語   | 特長加分     |
+--------------+--------+--------+--------+--------------+
| 林磊兒       |    148 |    150 |    147 |            0 |
| 喬英子       |    121 |    106 |    146 |            0 |
| 方一凡       |     70 |     90 |     59 |          200 |
| 陳哈哈       |    109 |     92 |     80 |            0 |
+--------------+--------+--------+--------+--------------+
4 rows in set (0.00 sec)

好，下面我們一起來看看SQL是如何編寫的

一、行轉列SQL寫法

方法一、使用case..when..then進行 行轉列

ELECT student_name,
    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 t_gaokao_score 
GROUP BY student_name;

這里如果不使用SUM()會報sql_mode=only_full_group_by相關錯誤，需要聚合函數和group by連用或使用distinct才可以解決。

  其實，加了SUM()是為了能夠使用GROUP BY根據student_name進行分組，每一個student_name對應的subject="語文"的記錄畢竟只有一條，所以SUM() 的值就等于對應那一條記錄的score的值。當然，也可以換成MAX()。

方法二、使用IF()進行 行轉列：

ELECT student_name,
    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 t_gaokao_score 
GROUP BY student_name;

該方法將IF(subject='語文',score,0)作為條件，通過student_name進行分組，對分組后所有subject='語文’的記錄的score字段進行SUM()操作，如果score沒有值則默認為0。

這種方式和case..when..then方法原理相同，相比更加簡潔明了，建議使用。

二、如果領導@你，讓你在結果集中加上總數列呢？

友情提示：我們工作中處理行轉列數據時，盡量都把總數、平均數等加上，方便領導查閱，省得他循環BB你。

話說，你還記得上學時的成績表是啥樣的么？你一般從上往下看還是從下往上看呢？文末投票，快來給大家樂呵樂呵！

寫法：利用SUM(IF()) 生成列，WITH ROLLUP 生成匯總列和行,并利用 IFNULL將匯總行標題顯示為總數

SELECT IFNULL(student_name,'總數') AS student_name,
    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 '總數' 
FROM t_gaokao_score
GROUP BY student_name WITH ROLLUP;

查詢結果：

+--------------+--------+--------+--------+--------------+--------+
| student_name | 語文   | 數學   | 英語   | 特長加分     | 總數   |
+--------------+--------+--------+--------+--------------+--------+
| 喬英子       |    121 |    106 |    146 |            0 |    373 |
| 方一凡       |     70 |     90 |     59 |          200 |    419 |
| 林磊兒       |    148 |    150 |    147 |            0 |    445 |
| 陳哈哈       |    113 |    116 |     80 |            0 |    309 |
| 總數         |    452 |    462 |    432 |          200 |   1546 |
+--------------+--------+--------+--------+--------------+--------+
5 rows in set, 1 warning (0.00 sec)

三、領導又雙叒叕@你改需求

讓你把分值轉化為具體內容顯示（優秀、良好、普通、差），430分以上重點大學，400分以上一本，350分及以上二本，350以下搬磚，該怎么寫呢？

  這里我們就需要case when嵌套一下了，看著高大上，其實就是普通的嵌套而已。在第一層查出分組后的各科分數，在第二層替換成等級即可。

SELECT student_name,
MAX(  
        CASE subject  
        WHEN '語文' THEN  
            (  
                CASE  
                WHEN score - (select avg(score) from t_gaokao_score where subject='語文') > 20 THEN  
                    '優秀'  
                WHEN score - (select avg(score) from t_gaokao_score where subject='語文') > 10 THEN  
                    '良好'  
                WHEN score - (select avg(score) from t_gaokao_score where subject='語文') >= 0 THEN  
                    '普通'  
                ELSE  
                    '差'  
                END  
            )  
        END  
    ) as '語文', 
MAX(  
        CASE subject  
        WHEN '數學' THEN  
            (  
                CASE  
                WHEN score - (select avg(score) from t_gaokao_score where subject='數學') > 20 THEN  
                    '優秀'  
                WHEN score - (select avg(score) from t_gaokao_score where subject='數學') > 10 THEN  
                    '良好'  
                WHEN score - (select avg(score) from t_gaokao_score where subject='數學') >= 0 THEN  
                    '普通'  
                ELSE  
                    '差'  
                END  
            )  
        END  
    ) as '數學',
MAX(  
        CASE subject  
        WHEN '英語' THEN  
            (  
                CASE  
                WHEN score - (select avg(score) from t_gaokao_score where subject='英語') > 20 THEN  
                    '優秀'  
                WHEN score - (select avg(score) from t_gaokao_score where subject='英語') > 10 THEN  
                    '良好'  
                WHEN score - (select avg(score) from t_gaokao_score where subject='英語') >= 0 THEN  
                    '普通'  
                ELSE  
                    '差'  
                END  
            )  
        END  
    ) as '英語',
SUM(score) as '總分',
(CASE WHEN SUM(score) > 430 THEN '重點大學'  
      WHEN SUM(score) > 400 THEN '一本'  
      WHEN SUM(score) > 350 THEN '二本'  
      ELSE '工地搬磚' 
      END ) as '結果'
FROM t_gaokao_score 
GROUP BY student_name 
ORDER BY SUM(score) desc;

我們來看一下輸出結果：

+--------------+--------+--------+--------+--------+--------------+
| student_name | 語文   | 數學   | 英語   | 總分   | 結果         |
+--------------+--------+--------+--------+--------+--------------+
| 林磊兒       | 優秀   | 優秀   | 優秀   |    445 | 重點大學     |
| 方一凡       | 差     | 差     | 差     |    419 | 一本         |
| 喬英子       | 普通   | 差     | 優秀   |    373 | 二本         |
| 陳哈哈       | 普通   | 普通   | 差     |    309 | 工地搬磚     |
+--------------+--------+--------+--------+--------+--------------+
4 rows in set (0.00 sec)

過來人的經驗來看，老實孩子最吃虧，早知道他娘的走藝體了~

附錄：創建表結構&測試數據SQL

表結構：

DROP TABLE IF EXISTS `t_gaokao_score`;
CREATE TABLE `t_gaokao_score`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '學生姓名',
  `subject` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '科目',
  `score` double NULL DEFAULT NULL COMMENT '成績',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

導入測試數據：

INSERT INTO `t_gaokao_score` VALUES 
(1, '林磊兒', '語文', 148),
(2, '林磊兒', '數學', 150),
(3, '林磊兒', '英語', 147),
(4, '喬英子', '語文', 121),
(5, '喬英子', '數學', 106),
(6, '喬英子', '英語', 146),
(7, '方一凡', '語文', 70),
(8, '方一凡', '數學', 90),
(9, '方一凡', '英語', 59),
(10, '方一凡', '特長加分', 200),
(11, '陳哈哈', '語文', 109),
(12, '陳哈哈', '數學', 92),
(13, '陳哈哈', '英語', 80);

“MySQL行轉列的方法是什么”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！