一、创建表结构和导入测试数据的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()
