查询单科最高分的记录
如表
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
(NameNvarchar(10),
EnglishInt,
ChineseInt,
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
/*
NameScore
李四100
NameScoreCourse
王五90English
李四100Chinese
王五98Math
*/
[解决办法]
如果你的是SQL 2000，並打了補丁，可以試下如下語句
Select * From (
Select Top 1 Name, English As Score, 'English ' As Course From Student Order By Score Desc
Union All
Select Top 1 Name, Chinese As Score, 'Chinese ' As Course From Student Order By Score Desc
Union All
Select Top 1 Name, Math As Score, 'Math ' As Course From Student Order By Score Desc ) A
