查询单科最高分的记录
如表    
Name             English         Chinese         Math    
张三                 10                   20                 30    
李四                 8                     100               65    
王五                 90                   99                 98    
甲六                 89                   90                 23    
 
 
查询单科最高分的记录,有以下两种情况
1.不指定哪一科，只要是最高分.(在此例中返回的是李四这条记录,因Chinese分数是在所有人中所有课目最高分).用一条SQL语句实现
2.一次性分别返回每一科的最高分。(在此例中会返回三条记录因有三科)用一条SQL语句实现

请问上面两种情况分别如何解？谢谢


------解决方案--------------------
2.一次性分别返回每一科的最高分。(在此例中会返回三条记录因有三科)用一条SQL语句实现
SELECT * FROM TABLENAME A
WHERE NOT EXISTS (
SELECT 1 FROM TABLENAME
WHERE English> A.English
)
OR NOT EXISTS (
SELECT 1 FROM TABLENAME
WHERE Chinese> A.Chinese
)
OR NOT EXISTS (
SELECT 1 FROM TABLENAME
WHERE Math> A.Math
)


------解决方案--------------------
1.不指定哪一科，只要是最高分.(在此例中返回的是李四这条记录,因Chinese分数是在所有人中所有课目最高分).用一条SQL语句实现

select a.* from tablename a,(
select max(SC) as sc from (
SELECT English AS SC FROM TABLENAME
UNION ALL
SELECT Chinese AS SC FROM TABLENAME
UNION ALL
SELECT Math AS SC FROM TABLENAME
) as t
) as m
where a.English=m.sc
or a.Chinese=m.sc
or a.Math=m.sc

------解决方案--------------------
Create Table Student
(Name Nvarchar(10),
English Int,
Chinese Int,
Math Int)
Insert Student Select N '张三 ', 10, 20, 30
Union All Select N '李四 ', 8, 100, 65
Union All Select N '王五 ', 90, 99, 98
Union All Select N '甲六 ', 89, 90, 23
GO
--1.
Select Top 1 * From
(Select Name, English As Score From Student
Union All
Select Name, Chinese As Score From Student
Union All
Select Name, Math As Score From Student) A
Order By Score Desc

--2.
Select * From (
Select Top 1 * From
(Select Name, English As Score, 'English ' As Course From Student) A Order By Score Desc ) B
Union All
Select * From (
Select Top 1 * From
(Select Name, Chinese As Score, 'Chinese ' As Course From Student) A Order By Score Desc ) B
Union All
Select * From (
Select Top 1 * From
(Select Name, Math As Score, 'Math ' As Course From Student) A Order By Score Desc) B
GO
Drop Table Student
--Result
/*
Name Score
李四 100

Name Score Course
王五 90 English
李四 100 Chinese
王五 98 Math
*/
------解决方案--------------------
如果你的是SQL 2000，並打了補丁，可以試下如下語句

Select * From (
Select Top 1 Name, English As Score, 'English ' As Course From Student Or