选出每个学生的最大成绩的sql语句

name score1 score2 score3

A 70 80 90
B 85 75 95
C 69 74 58
D 54 36 21

如上，学生A,B,C,D分别有3科的成绩，现在想查出每个学生的最高分：

name   maxscore

A          90

B          95

C          74

D          54

 

遇到这种列变行的当然就是用union了。。

select name, max(score) as ms  from (
select score1 as score, name , 'score1' as course from student
union all
select score2 as score, name  , 'score2' as course  from student
union all
select score3 as score, name , 'score3' as course  from student) t
group by t.name

 

先是把所有列变成name score的两列，然后就选最大的。

 

问题是，如果要把科目也选上，那要怎么写。

想了好久没想到，只能用笨方法：

select t2.name,t2.ms, t3.course from
(select name, max(score) as ms  from (
select score1 as score, name , 'score1' as course from student
union all
select score2 as score, name  , 'score2' as course  from student
union all
select score3 as score, name , 'score3' as course  from student) t
group by t.name) t2 left join (

select score1 as score, name , 'score1' as course from student
union all
select score2 as score, name  , 'score2' as course  from student
union all
select score3 as score, name , 'score3' as course  from student) t3
on t2.name = t3.name and t2.ms=t3.score
这样结果是出来了，但是效率却不高，而且t和t3的代码是一样的。

A 90 score3
B 95 score3
C 74 score2
D 54 score1

 

不知道什么优化的方法？