摘要：1、mysql行轉列：使用 case…when…then 或 SUM(IF()) 2、使用SUM(IF()) 生成列 + WITH ROLLUP 生成匯總行 3、使用SUM(IF()) 生成列，直接生成匯總結果，不再利用子查詢...

一、創建表結構和導入測試數據的SQL：

DROP TABLE IF EXISTS `yzm_score`;
CREATE TABLE `yzm_score`  (
  `id` int(11) 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`)
) ENGINE = MyISAM AUTO_INCREMENT = 14 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

INSERT INTO `yzm_score` VALUES (1, '張三', '語文', 148);
INSERT INTO `yzm_score` VALUES (2, '張三', '數學', 150);
INSERT INTO `yzm_score` VALUES (3, '張三', '英語', 147);
INSERT INTO `yzm_score` VALUES (4, '李四', '語文', 121);
INSERT INTO `yzm_score` VALUES (5, '李四', '數學', 106);
INSERT INTO `yzm_score` VALUES (6, '李四', '英語', 146);
INSERT INTO `yzm_score` VALUES (7, '王五', '語文', 70);
INSERT INTO `yzm_score` VALUES (8, '王五', '數學', 90);
INSERT INTO `yzm_score` VALUES (9, '王五', '英語', 59);
INSERT INTO `yzm_score` VALUES (10, '王五', '特長加分', 200);
INSERT INTO `yzm_score` VALUES (11, '趙六', '語文', 109);
INSERT INTO `yzm_score` VALUES (12, '趙六', '數學', 92);
INSERT INTO `yzm_score` VALUES (13, '趙六', '英語', 80);

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

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

SELECT 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 yzm_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()進行 行轉列

SELECT 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 yzm_score 
GROUP BY student_name;

該方法將IF(subject='語文',score,0)作為條件，通過student_name進行分組，對分組后所有subject='語文’的記錄的score字段進行SUM()操作，如果score沒有值則默認為0。這種方式和case..when..then方法原理相同，相比更加簡潔明了，建議使用。

二、那如何在結果集中加上總數列呢？

寫法：利用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 yzm_score
GROUP BY student_name WITH ROLLUP;

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

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

SELECT student_name,
MAX(  
        CASE subject  
        WHEN '語文' THEN  
            (  
                CASE  
                WHEN score - (select avg(score) from yzm_score where subject='語文') > 20 THEN  
                    '優秀'  
                WHEN score - (select avg(score) from yzm_score where subject='語文') > 10 THEN  
                    '良好'  
                WHEN score - (select avg(score) from yzm_score where subject='語文') >= 0 THEN  
                    '普通'  
                ELSE  
                    '差'  
                END  
            )  
        END  
    ) as '語文', 
MAX(  
        CASE subject  
        WHEN '數學' THEN  
            (  
                CASE  
                WHEN score - (select avg(score) from yzm_score where subject='數學') > 20 THEN  
                    '優秀'  
                WHEN score - (select avg(score) from yzm_score where subject='數學') > 10 THEN  
                    '良好'  
                WHEN score - (select avg(score) from yzm_score where subject='數學') >= 0 THEN  
                    '普通'  
                ELSE  
                    '差'  
                END  
            )  
        END  
    ) as '數學',
MAX(  
        CASE subject  
        WHEN '英語' THEN  
            (  
                CASE  
                WHEN score - (select avg(score) from yzm_score where subject='英語') > 20 THEN  
                    '優秀'  
                WHEN score - (select avg(score) from yzm_score where subject='英語') > 10 THEN  
                    '良好'  
                WHEN score - (select avg(score) from yzm_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 yzm_score 
GROUP BY student_name 
ORDER BY SUM(score) desc;

總結：

1、行轉列：使用 case…when…then 或 SUM(IF())

2、使用SUM(IF()) 生成列 + WITH ROLLUP 生成匯總行

3、使用SUM(IF()) 生成列，直接生成匯總結果，不再利用子查詢

4、使用SUM(IF()) 生成列 + UNION 生成匯總行,并利用 IFNULL 將匯總行標題顯示為 Total

5、合并字段顯示使用：group_concat()