1)创建表结构&测试数据SQL
表结构:
DROP TABLE IF EXISTS `t_gaokao_score`;
CREATE TABLE `t_gaokao_score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`student_name` varchar(20) NOT NULL COMMENT '学生姓名',
`subject` varchar(20) DEFAULT NULL COMMENT '科目',
`score` double DEFAULT NULL COMMENT '成绩',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
导入测试数据:
INSERT INTO `yus_test`.`t_gaokao_score` (`id`, `student_name`, `subject`, `score`) VALUES (1, '镇镇', '语文', 148);
INSERT INTO `yus_test`.`t_gaokao_score` (`id`, `student_name`, `subject`, `score`) VALUES (2, '镇镇', '数学', 146);
INSERT INTO `yus_test`.`t_gaokao_score` (`id`, `student_name`, `subject`, `score`) VALUES (3, '镇镇', '英语', 149);
INSERT INTO `yus_test`.`t_gaokao_score` (`id`, `student_name`, `subject`, `score`) VALUES (4, '龙龙', '语文', 124);
INSERT INTO `yus_test`.`t_gaokao_score` (`id`, `student_name`, `subject`, `score`) VALUES (5, '龙龙', '数学', 121);
INSERT INTO `yus_test`.`t_gaokao_score` (`id`, `student_name`, `subject`, `score`) VALUES (6, '龙龙', '英语', 114);
INSERT INTO `yus_test`.`t_gaokao_score` (`id`, `student_name`, `subject`, `score`) VALUES (7, '小红', '语文', 54);
INSERT INTO `yus_test`.`t_gaokao_score` (`id`, `student_name`, `subject`, `score`) VALUES (8, '小红', '数学', 76);
INSERT INTO `yus_test`.`t_gaokao_score` (`id`, `student_name`, `subject`, `score`) VALUES (9, '小红', '英语', 31);
INSERT INTO `yus_test`.`t_gaokao_score` (`id`, `student_name`, `subject`, `score`) VALUES (10, '小红', '特长加分', 199);
INSERT INTO `yus_test`.`t_gaokao_score` (`id`, `student_name`, `subject`, `score`) VALUES (11, '刘一手', '语文', 102);
INSERT INTO `yus_test`.`t_gaokao_score` (`id`, `student_name`, `subject`, `score`) VALUES (12, '刘一手', '数学', 92);
INSERT INTO `yus_test`.`t_gaokao_score` (`id`, `student_name`, `subject`, `score`) VALUES (13, '刘一手', '英语', 89);
行转列SQL写法
1)使用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 t_gaokao_score
GROUP BY student_name;
2)使用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 t_gaokao_score
GROUP BY student_name;
3)结果集中加上总数列
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 t_gaokao_score
GROUP BY student_name WITH ROLLUP;
4)分值转化为具体内容显示(优秀、良好、普通、差),430分以上重点大学,400分以上一本,350分及以上二本,350以下搬砖
SELECT student_name,
MAX(
CASE subject
WHEN '语文' THEN
(
CASE
WHEN score - (select avg(score) from t_gaokao_score where subject='语文') > 20 THEN
'优秀'
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 '数学' THEN
(
CASE
WHEN score - (select avg(score) from t_gaokao_score where subject='数学') > 20 THEN
'优秀'
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 '英语' THEN
(
CASE
WHEN score - (select avg(score) from t_gaokao_score where subject='英语') > 20 THEN
'优秀'
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 '重点大学'
WHEN SUM(score) > 400 THEN '一本'
WHEN SUM(score) > 350 THEN '二本'
ELSE '工地搬砖'
END ) as '结果'
FROM t_gaokao_score
GROUP BY student_name
ORDER BY SUM(score) desc;