遇到的一个sql的面试题

表结构

表名：stu

将stu 改为 如下显示方式

 

方法1：

select table1.studentname,table1.语文,table2.数学,table3.英语 from ( select studentname, isnull(score,0) as '语文'  from stu where objname='语文') as table1
inner join (select studentname, isnull(score,0) as '数学'  from stu where objname='数学' )as table2 on table2.studentname=table1.studentname
inner join (select studentname, isnull(score,0) as '英语'  from stu where objname='英语' )as table3 on table3.studentname=table1.studentname

方法2：

select A.studentname,isnull(A.score,0) as 语文,isnull(B.score,0) as 数学,isnull(C.score,0) as 英语
from  stu A,stu B,stu C
where A.studentname=B.studentname and B.studentname=C.studentname
and A.objname='语文' and B.objname='数学' and C.objname='英语'