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

首先，創(chuàng)建表結(jié)構(gòu)和導(dǎo)入測(cè)試數(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 '成績(jī)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
#導(dǎo)入測(cè)試數(shù)據(jù)
INSERT INTO `t_gaokao_score` VALUES 
(1, '林磊兒', '語(yǔ)文', 148),
(2, '林磊兒', '數(shù)學(xué)', 150),
(3, '林磊兒', '英語(yǔ)', 147),
(4, '喬英子', '語(yǔ)文', 121),
(5, '喬英子', '數(shù)學(xué)', 106),
(6, '喬英子', '英語(yǔ)', 146),
(7, '方一凡', '語(yǔ)文', 70),
(8, '方一凡', '數(shù)學(xué)', 90),
(9, '方一凡', '英語(yǔ)', 59),
(10, '方一凡', '特長(zhǎng)加分', 200),
(11, '陳哈哈', '語(yǔ)文', 109),
(12, '陳哈哈', '數(shù)學(xué)', 92),
(13, '陳哈哈', '英語(yǔ)', 80);


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


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

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

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

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

#來(lái)源公眾號(hào)【碼農(nóng)編程進(jìn)階筆記】
SELECT student_name,
    SUM(CASE `subject` WHEN '語(yǔ)文' THEN score ELSE 0 END) as '語(yǔ)文',
    SUM(CASE `subject` WHEN '數(shù)學(xué)' THEN score ELSE 0 END) as '數(shù)學(xué)',
    SUM(CASE `subject` WHEN '英語(yǔ)' THEN score ELSE 0 END) as '英語(yǔ)',
    SUM(CASE `subject` WHEN '特長(zhǎng)加分' THEN score ELSE 0 END) as '特長(zhǎng)加分' 
FROM t_gaokao_score 
GROUP BY student_name;

??這里如果不使用SUM()會(huì)報(bào)sql_mode=only_full_group_by相關(guān)錯(cuò)誤，需要聚合函數(shù)和group by連用或使用distinct才可以解決。


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

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

#來(lái)源公眾號(hào)【碼農(nóng)編程進(jìn)階筆記】
SELECT student_name,
    SUM(IF(`subject`='語(yǔ)文',score,0)) as '語(yǔ)文',
    SUM(IF(`subject`='數(shù)學(xué)',score,0)) as '數(shù)學(xué)',
    SUM(IF(`subject`='英語(yǔ)',score,0)) as '英語(yǔ)',
    SUM(IF(`subject`='特長(zhǎng)加分',score,0)) as '特長(zhǎng)加分' 
FROM t_gaokao_score 
GROUP BY student_name;
??該方法將IF(subject='語(yǔ)文',score,0)作為條件，通過(guò)student_name進(jìn)行分組，對(duì)分組后所有subject='語(yǔ)文’的記錄的score字段進(jìn)行SUM()操作，如果score沒(méi)有值則默認(rèn)為0。這種方式和case..when..then方法原理相同，相比更加簡(jiǎn)潔明了，建議使用。


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

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




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

#來(lái)源公眾號(hào)【碼農(nóng)編程進(jìn)階筆記】
SELECT IFNULL(student_name,'總數(shù)') AS student_name,
    SUM(IF(`subject`='語(yǔ)文',score,0)) AS '語(yǔ)文',
    SUM(IF(`subject`='數(shù)學(xué)',score,0)) AS '數(shù)學(xué)',
    SUM(IF(`subject`='英語(yǔ)',score,0)) AS '英語(yǔ)',
    SUM(IF(`subject`='特長(zhǎng)加分',score,0)) AS '特長(zhǎng)加分',
    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分以上重點(diǎn)大學(xué)，400分以上一本，350分及以上二本，350以下搬磚，該怎么寫(xiě)呢？


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

SELECT student_name,
MAX(  
        CASE subject  
        WHEN '語(yǔ)文' THEN  
            (  
                CASE  
                WHEN score - (select avg(score) from t_gaokao_score where subject='語(yǔ)文') > 20 THEN  
                    '優(yōu)秀'  
                WHEN score - (select avg(score) from t_gaokao_score where subject='語(yǔ)文') > 10 THEN  
                    '良好'  
                WHEN score - (select avg(score) from t_gaokao_score where subject='語(yǔ)文') >= 0 THEN  
                    '普通'  
                ELSE  
                    '差'  
                END  
            )  
        END  
    ) as '語(yǔ)文', 
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 '英語(yǔ)' THEN  
            (  
                CASE  
                WHEN score - (select avg(score) from t_gaokao_score where subject='英語(yǔ)') > 20 THEN  
                    '優(yōu)秀'  
                WHEN score - (select avg(score) from t_gaokao_score where subject='英語(yǔ)') > 10 THEN  
                    '良好'  
                WHEN score - (select avg(score) from t_gaokao_score where subject='英語(yǔ)') >= 0 THEN  
                    '普通'  
                ELSE  
                    '差'  
                END  
            )  
        END  
    ) as '英語(yǔ)',
SUM(score) as '總分',
(CASE WHEN SUM(score) > 430 THEN '重點(diǎn)大學(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;

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

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



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


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