MySQL行转列
一、新建成绩测试表:
CREATE TABLE `score` (
`student_no` varchar(13) DEFAULT NULL COMMENT '学号',
`subject_no` varchar(20) DEFAULT NULL COMMENT '科目',
`score` int(3) DEFAULT NULL COMMENT '成绩'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
二、创建存储过程批量插入测试数据:
delimiter //
CREATE PROCEDURE pro_insert()
BEGIN
DECLARE num int;
SET num =1;
WHILE num <10000 DO
INSERT INTO score (student_no,subject_no,score) VALUES
(CONCAT('00',num),'语文',FLOOR(70+RAND()*30)),
(CONCAT('00',num),'数学',FLOOR(70+RAND()*30)),
(CONCAT('00',num),'英语',FLOOR(70+RAND()*30));
SET num=num+1;
END WHILE;
end;
//
三、查询执行结果:
四、利用case when then end 将数据行转列
SELECT
student_no,
MAX(CASE subject_no WHEN '语文' THEN score else 0 END) AS 语文,
MAX(CASE subject_no WHEN '数学' THEN score else 0 END) AS 数学,
MAX(CASE subject_no WHEN '英语' THEN score else 0 END) AS 英语
FROM score
GROUP BY student_no;
转换后结果: