有关SQL的一道面试题

这是一个学生分数表

StudentName            StudySubject           SubjectScore

Peter                                 EN                               89

Peter                                 CH                               78

Peter                                 HO                               99

May                                   EN                               34

May                                   CH                               88

May                                   HO                               66

 

请用SQL 写出查询以下结果的脚本：

 

StudentName        EN         CH        HO

Peter                    89          78        99

May                      34          88        66

 

SELECT A.StudentName,A.SubjectScore as EN,B.SubjectScore as CH,C.SubjectScore as HO

From students A,students B,students C  

WHERE A.StudentName=B.StudentName AND B.StudentName=C.StudentName

AND A.StudySubject='语文' AND B.StudySubject='数学'  

AND C.StudySubject='英语'