mysql的行转列,列转行(但是对多列进行操作)
行转列
运行前:
运行后:
源sql:
-- 创建学生表 CREATE TABLE test.stu_score ( username VARCHAR(20), -- 学生姓名 subject VARCHAR(30), -- 科目 score FLOAT -- 成绩 ); -- 插入数据 use test; INSERT INTO stu_score SELECT '张三', '语文', 80; INSERT INTO stu_score SELECT '张三', '数学', 90; INSERT INTO stu_score SELECT '张三', '英语', 70; INSERT INTO stu_score SELECT '张三', '生物', 85; INSERT INTO stu_score SELECT '李四', '语文', 80; INSERT INTO stu_score SELECT '李四', '数学', 92; INSERT INTO stu_score SELECT '李四', '英语', 76; INSERT INTO stu_score SELECT '李四', '生物', 88; INSERT INTO stu_score SELECT '码农', '语文', 60; INSERT INTO stu_score SELECT '码农', '数学', 82; INSERT INTO stu_score SELECT '码农', '英语', 96; INSERT INTO stu_score SELECT '码农', '生物', 78; SELECT * from stu_score ; -- 行转列 -- 这里的max是为了将无数据的值设为0,避免出现null SELECT username , MAX(CASE subject WHEN '数学' THEN score ELSE 0 END ) 数学, MAX(CASE subject WHEN '语文' THEN score ELSE 0 END ) 语文, MAX(CASE subject WHEN '英语' THEN score ELSE 0 END ) 英语, MAX(CASE subject WHEN '生物' THEN score ELSE 0 END ) 生物 FROM stu_score GROUP BY username;
列转行
运行前:
运行后:
源sql:
CREATE TABLE test.stu_score2 ( username VARCHAR(20), -- 学生姓名 math int, -- 科目 chinese int, english int, biological int ); DROP TABLE test.stu_score2; -- 插入数据 use test; INSERT INTO stu_score2 SELECT '张三', 90, 80,70,85; INSERT INTO stu_score2 SELECT '李四', 92, 80,76,88; INSERT INTO stu_score2 SELECT '王五', 82, 60,96,78; SELECT * FROM test.stu_score2; -- 列转行 select username, '数学' COURSE , math as score from test.stu_score2 UNION select username, '语文' COURSE, chinese as SCORE from test.stu_score2 UNION select username, '英语' COURSE, english as SCORE from test.stu_score2 UNION select username, '生物' COURSE, biological as SCORE from test.stu_score2 order by username,COURSE;