使用 group by 查询前 N 行的 SQL 查询

Question

嗨，我尝试了很多方法来解决这个问题，但遗漏了一些东西..我有两张表 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

我需要返回每门类(class)得分最高的前 2 名学生的 Sid 和 Sname。如果存在，则返回每门类(class)所有男学生中得分最高的学生的 Sid 和 Sname。

这里的前 2 最高分不仅仅是 ex 组中的前两名记录:第一组的前 2 最高分是 90, 80 ,80 。

我需要这样放

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

我尝试了以下代码:

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

Answer 1

对于第一个问题(列出每门类(class)得分最高的前 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

对于你的第二个问题(每门类(class)的所有男生中得分最高的学生)，请执行以下操作:

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 

希望对您有所帮助。