Question

sql查询最大值？

查询Score表中的最高分的学生学号和课程号。
SNO CNO DEGREE
--- ----- ------------
103 3-245 86.0
105 3-245 75.0
109 3-245 68.0
103 3-105 92.0
105 3-105 88.0
109 3-105 76.0
101 3-105 64.0
107 3-105 91.0
108 3-105 78.0
101 6-166 85.0
107 6-106 79.0
108 6-166 81.0

查询最大值select max(degree) from score ;可以查处最大分数是92.
可是题目要的是最高分的学生学号和课程号！请大侠们指点下

Answer 1

select SNO,CNO from Score where degree=(select max(degree) from score)

或者

select top 1 SNO,CNO from Score order by degree desc

Answer 2

select
top
1
table_b.counta
from
(select
a,
count(*)
as
counta
from
table
group
by
a)
as
table_b
order
by
table_b.counta
desc

Answer 3

用如下语句可以查出各课程的最高分学号和科目号以及得分：

select sno,cno,degree from Score a where not exists
(select * from Score b where a.sno=b.sno and a.cno=b.cno and a.degree<b.degree)

Answer 4

SELECT A.SNo,A.CNo,A.Degree
FROM Score S JOIN (SELECT MAX(Degree) AS Degree FROM Score) AS T
ON S.Degree = T.Degree