mysql的行转列实现方法_袁志蒙个人博客

摘要：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);

看看我们行转列转完后的结果:

mysql的行转列实现方法

方法一：使用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;

mysql的行转列实现方法

三、如何把分值转化为具体内容显示（优秀、良好、普通、差），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;

mysql的行转列实现方法