MySQL 行轉列詳情

Posted on by

  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放到文章末尾瞭，自取~

一、行轉列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方面就是以上這些內容瞭，有疑問可以寫在評論區，哈哥會在摸魚的時候回復你~~`

附錄：創建表結構&測試數據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 行轉列詳情的文章就介紹到這瞭,更多相關MySQL 行轉列內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：