选择表中每个组中最高的前3位。

Question

有一张桌子，里面有老师、年数和课程。我必须找出每组课程中由老师教授的前三名(2005至2010年)。

我把我的表格按课程、年份、老师分类，如果我有，我会统计一下lesson1的课程。

lesson1  year1  teachera 20
lesson1  year1  teacherb 1
lesson1  year1  teacherd 10
lesson1  year1  teachere 5
lesson1  year1  teacherf 0
lesson1  year3  teacherc 1
lesson1  year4  teacherb 8

我必须得到20，10和5的year1和1的year3和8的year4

我有大约40节课

Answer 1

您应该使用分析功能来实现它。

就像下面

SELECT Lesson, Year, Name, Count
  FROM
(
  select Lesson, Year, Name, Count,
         DENSE_RANK() OVER (PARTITION BY Lesson, Year, Name ORDER BY Count DESC) as RK
  from A
)t
 WHERE RK <= 3;

Answer 2

如果您使用的是Oracle (当您标记问题时)，这将起作用：

SELECT *
  FROM
(
  select Lesson, Year, Name, Count,
         ROW_NUMBER() OVER (PARTITION BY Lesson, Year, Name ORDER BY Count DESC) as RN
  from A
)t
 WHERE RN <= 3;

它不会在MySQL中工作(就像您在sqlfiddle中创建它一样)，但是我相信您可以在那里做同样的事情。

Answer 3

我就是这么干的，谢谢

SELECT Lesson, Year, Name, Count
FROM
(
  SELECT Lesson, Year, Name, Count,
         RANK() OVER (PARTITION BY Lesson, Year, Name ORDER BY Count DESC) 
  as RK
  from A
) t
WHERE RK <= 3;