MySQL——行转列

MySQL——行转列 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;