列转行与行转列

mysql的行转列，列转行(但是对多列进行操作)

-- 创建学生表
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;

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;

mysql的行转列，列转行(但是对多列进行操作)

相关推荐