5分鐘搞懂MySQL - 行轉(zhuǎn)列

首先，創(chuàng)建表結(jié)構(gòu)和導(dǎo)入測試數(shù)據(jù)的SQL
#創(chuàng)建表結(jié)構(gòu)
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 '學(xué)生姓名',
  `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;
#導(dǎo)入測試數(shù)據(jù)
INSERT INTO `t_gaokao_score` VALUES 
(1, '林磊兒', '語文', 148),
(2, '林磊兒', '數(shù)學(xué)', 150),
(3, '林磊兒', '英語', 147),
(4, '喬英子', '語文', 121),
(5, '喬英子', '數(shù)學(xué)', 106),
(6, '喬英子', '英語', 146),
(7, '方一凡', '語文', 70),
(8, '方一凡', '數(shù)學(xué)', 90),
(9, '方一凡', '英語', 59),
(10, '方一凡', '特長加分', 200),
(11, '陳哈哈', '語文', 109),
(12, '陳哈哈', '數(shù)學(xué)', 92),
(13, '陳哈哈', '英語', 80);


然后，我們看一下咱們的測試表數(shù)據(jù)和預(yù)期查詢的結(jié)果。


#來源公眾號【碼農(nóng)編程進階筆記】
mysql> SELECT * FROM t_gaokao_score;
+----+--------------+--------------+-------+
| id | student_name | subject      | score |
+----+--------------+--------------+-------+
|  1 | 林磊兒       | 語文         |   148 |
|  2 | 林磊兒       | 數(shù)學(xué)         |   150 |
|  3 | 林磊兒       | 英語         |   147 |
|  4 | 喬英子       | 語文         |   121 |
|  5 | 喬英子       | 數(shù)學(xué)         |   106 |
|  6 | 喬英子       | 英語         |   146 |
|  7 | 方一凡       | 語文         |    70 |
|  8 | 方一凡       | 數(shù)學(xué)         |    90 |
|  9 | 方一凡       | 英語         |    59 |
| 10 | 方一凡       | 特長加分     |   200 |
| 11 | 陳哈哈       | 語文         |   109 |
| 12 | 陳哈哈       | 數(shù)學(xué)         |    92 |
| 13 | 陳哈哈       | 英語         |    80 |
+----+--------------+--------------+-------+
13 rows in set (0.00 sec)

看看我們行轉(zhuǎn)列轉(zhuǎn)完后的結(jié)果:

一、行轉(zhuǎn)列SQL寫法

方法一：使用case..when..then進行 行轉(zhuǎn)列

#來源公眾號【碼農(nóng)編程進階筆記】
SELECT student_name,
    SUM(CASE `subject` WHEN '語文' THEN score ELSE 0 END) as '語文',
    SUM(CASE `subject` WHEN '數(shù)學(xué)' THEN score ELSE 0 END) as '數(shù)學(xué)',
    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相關(guān)錯誤，需要聚合函數(shù)和group by連用或使用distinct才可以解決。


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

圖片方法二：使用IF()進行 行轉(zhuǎn)列：

#來源公眾號【碼農(nóng)編程進階筆記】
SELECT student_name,
    SUM(IF(`subject`='語文',score,0)) as '語文',
    SUM(IF(`subject`='數(shù)學(xué)',score,0)) as '數(shù)學(xué)',
    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方法原理相同，相比更加簡潔明了，建議使用。


二、如果領(lǐng)導(dǎo)@你，讓你在結(jié)果集中加上總數(shù)列呢？

友情提示：我們工作中處理行轉(zhuǎn)列數(shù)據(jù)時，盡量都把總數(shù)、平均數(shù)等加上，方便領(lǐng)導(dǎo)查閱，省得他循環(huán)BB你。
寫法：利用SUM(IF()) 生成列，




WITH ROLLUP 生成匯總列和行,并利用 IFNULL將匯總行標題顯示為總數(shù)

#來源公眾號【碼農(nóng)編程進階筆記】
SELECT IFNULL(student_name,'總數(shù)') AS student_name,
    SUM(IF(`subject`='語文',score,0)) AS '語文',
    SUM(IF(`subject`='數(shù)學(xué)',score,0)) AS '數(shù)學(xué)',
    SUM(IF(`subject`='英語',score,0)) AS '英語',
    SUM(IF(`subject`='特長加分',score,0)) AS '特長加分',
    SUM(score) AS '總數(shù)' 
FROM t_gaokao_score
GROUP BY student_name WITH ROLLUP;

查詢結(jié)果：

圖片三、領(lǐng)導(dǎo)又雙叒叕@你改需求

??讓你把分值轉(zhuǎn)化為具體內(nèi)容顯示（優(yōu)秀、良好、普通、差），430分以上重點大學(xué)，400分以上一本，350分及以上二本，350以下搬磚，該怎么寫呢？


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

SELECT student_name,
MAX(  
        CASE subject  
        WHEN '語文' THEN  
            (  
                CASE  
                WHEN score - (select avg(score) from t_gaokao_score where subject='語文') > 20 THEN  
                    '優(yōu)秀'  
                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 '數(shù)學(xué)' THEN  
            (  
                CASE  
                WHEN score - (select avg(score) from t_gaokao_score where subject='數(shù)學(xué)') > 20 THEN  
                    '優(yōu)秀'  
                WHEN score - (select avg(score) from t_gaokao_score where subject='數(shù)學(xué)') > 10 THEN  
                    '良好'  
                WHEN score - (select avg(score) from t_gaokao_score where subject='數(shù)學(xué)') >= 0 THEN  
                    '普通'  
                ELSE  
                    '差'  
                END  
            )  
        END  
    ) as '數(shù)學(xué)',
MAX(  
        CASE subject  
        WHEN '英語' THEN  
            (  
                CASE  
                WHEN score - (select avg(score) from t_gaokao_score where subject='英語') > 20 THEN  
                    '優(yōu)秀'  
                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 '重點大學(xué)'  
      WHEN SUM(score) > 400 THEN '一本'  
      WHEN SUM(score) > 350 THEN '二本'  
      ELSE '工地搬磚' 
      END ) as '結(jié)果'
FROM t_gaokao_score 
GROUP BY student_name 
ORDER BY SUM(score) desc;

我們來看一下輸出結(jié)果：

圖片好了，SQL方面就是以上這些內(nèi)容了，有疑問可以寫在評論區(qū)留言！



作者：碼農(nóng)編程進階筆記


歡迎關(guān)注微信公眾號 ：碼農(nóng)編程進階筆記