SELECT * FROM student_grade;
select t1.num,t1.name,t1.chinese,t2.math,t3.english from
(SELECT a.num,a.name,a.sex,b.grade as chinese FROM student_grade as a
inner join student_grade as b
on a.num = b.num and a.course = b.course
where b.course = '语文') as t1,
(SELECT a.num,a.name,a.sex,b.grade as math FROM student_grade as a
inner join student_grade as b
on a.num = b.num and a.course = b.course
where b.course = '数学') as t2,
(SELECT a.num,a.name,a.sex,b.grade as english FROM student_grade as a
inner join student_grade as b
on a.num = b.num and a.course = b.course
where b.course = '英语') as t3
select t1.num,t1.name,t1.chinese,t2.math,t3.english from
(SELECT a.num,a.name,a.sex,b.grade as chinese FROM student_grade as a
inner join student_grade as b
on a.num = b.num and a.course = b.course
where b.course = '语文') as t1,
(SELECT a.num,a.name,a.sex,b.grade as math FROM student_grade as a
inner join student_grade as b
on a.num = b.num and a.course = b.course
where b.course = '数学') as t2,
(SELECT a.num,a.name,a.sex,b.grade as english FROM student_grade as a
inner join student_grade as b
on a.num = b.num and a.course = b.course
where b.course = '英语') as t3
where t1.num = t2.num and t2.num = t3.num;
原表
1 01 张三 男 语文 81 2 01 张三 男 数学 82 3 01 张三 男 英语 83 4 02 李四 男 语文 71 5 02 李四 男 数学 72 6 02 李四 男 英语 73 7 03 小红 女 语文 74 8 03 小红 女 数学 75 9 03 小红 女 英语 76 10 04 小明 男 语文 84 11 04 小明 男 数学 85 12 04 小明 男 英语 86
效果
01 张三 81 82 83 02 李四 71 72 73 03 小红 74 75 76 04 小明 84 85 86