MySQL行转列完整SQL示例_学亮编程手记

-- SQL行转列
create table student_scores(
	username VARCHAR(10),-- 学生姓名
	subject varchar(10),-- 科目名称
	score int(3)-- 成绩
)

INSERT into student_scores values('张三','语文',86);
INSERT into student_scores values('张三','数学',96);
INSERT into student_scores values('张三','英语',76);
INSERT into student_scores values('李四','语文',59);
INSERT into student_scores values('李四','数学',61);
INSERT into student_scores values('李四','英语',88);


-- 行转列
select username,
max(case subject when '语文' THEN score end) as '语文',
max(case subject when '数学' THEN score end) as '数学',
max(case subject when '英语' THEN score end) as '英语'
FROM student_scores
GROUP BY username
UNION
select username,sum(chinese),sum(math),sum(english) from(
select '单科总成绩' username,
sum(case subject when '语文' THEN score end) as chinese,
sum(case subject when '数学' THEN score end) as math,
sum(case subject when '英语' THEN score end) as english
FROM student_scores
GROUP BY username )t
GROUP BY username-- SQL行转列
create table student_scores(
	usernam