oracle 使用分组依据的前 N 行的 Sql 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5094226/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sql query for Top N rows using a group by
提问by user630605
Hi i tried many ways to solve this but missing some thing.. I have two tables Student and Score
嗨，我尝试了很多方法来解决这个问题，但遗漏了一些东西.. 我有两个表 Student 和 Score
Sid Cid Score
6 1 90
1 1 80
4 1 80
3 1 70
2 1 60
6 2 80
1 2 70
2 2 60
4 2 60
5 2 50
4 3 80
7 3 80
6 3 70
8 3 60
2 3 50
Sid Sname Sbday Ssex
1 As 1980 female
2 Al 1986 male
3 An 1989 male
4 ja 1986 male
5 ma 1983 female
6 phi 1986 male
7 Geo 1993 male
8 lil 1990 female
9 cha 1993 male
I need to Return Sid and Sname of the students who have the top 2 highest score for each course. If existed, return Sid and Sname of the student who has the highest score among all the male students for each course.
我需要返回每门课程得分最高的学生的 Sid 和 Sname。如果存在，则返回每门课程所有男学生中得分最高的学生的 Sid 和 Sname。
Here top 2 highest score is not just top two records in a group for ex : top 2 highest score in 1st group is 90, 80 ,80 .
这里的前 2 名最高分不仅仅是一组中的前两名记录，例如：第一组的前 2 名最高分是 90, 80 ,80 。
I need out put like this
我需要像这样
Sid Cid Score
6 1 90
1 1 80
4 1 80
6 2 80
1 2 70
2 2 60
4 2 60
4 3 80
7 3 80
6 3 70
I tried the following code :
我尝试了以下代码：
select A.Sid , S.SNAME, Score,Cid
from Score a,STUDENTS S
where 2 >(select count(Cid)
from Score
where Cid=a.Cid
and Score>a.Score)
AND A.SID = S.SID
order by Cid,Score desc
回答by Lamak
For the first question (list the students who have the top 2 highest score for each course), you should try this:
对于第一个问题（列出每门课程得分最高的前 2 名学生），你应该试试这个：
SELECT SC.Sid , ST.SNAME, SC.Score, SC.Cid
FROM ( SELECT *, DENSE_RANK() OVER(PARTITION BY Cid ORDER BY Score DESC) TopScore
FROM Score) AS SC
INNER JOIN Students AS ST
ON SC.Sid = ST.Sid
WHERE SC.TopScore <= 2
For your second quetion (the student who has the highest score among all the male students for each course), do the following:
对于你的第二个问题（每门课程所有男学生中得分最高的学生），请执行以下操作：
SELECT A.Sid , A.SNAME, A.Score, A.Cid
FROM ( SELECT SC.Sid , ST.SNAME, SC.Score, SC.Cid, DENSE_RANK() OVER(PARTITION BY Cid ORDER BY Score DESC) TopScore
FROM Score AS SC
INNER JOIN Students AS ST
ON SC.Sid = ST.Sid
WHERE ST.Ssex = 'male') A
WHERE A.TopScore = 1
Hope it helps.
希望能帮助到你。
