SQL Group-By并返回有限结果

Question

最近我在一次面试中被问到一个SQL问题。要求是在一个表格中找到“最热情的学生”，其中包含学生的ID、课程号和年数。查询应该返回每年上课次数最多的学生，并且只返回这些记录。(我忽略了不同的学生在给定的一年中上课次数相同的情况，因为我没有得到这样的要求。可能还有其他条件来满足这种关系，所以我已经避免了这种情况，我只想让一个查询来解决这个特殊的情况，在这种情况下，每年没有同样热情的学生)。

之后，我构建了一个小表，试图解决这个查询。我的桌子有三个字段：

CREATE TABLE students (
student_no    INT,
course_no     INT,
year          varchar(4));

(这张桌子真的很简单。我知道一年不应该像这样储存，等等。我不想被挂在桌子的格式上。这只是为了尽可能简单地支持这个练习。)

我创建的数据是：

'1', '1', '2000'
'1', '2', '2000'
'1', '3', '2000'
'2', '1', '2000'
'2', '2', '2000'
'2', '3', '2000'
'2', '4', '2000'
'1', '2', '2001'
'1', '1', '2001'
'1', '3', '2001'
'1', '4', '2001'
'2', '1', '2001'
'2', '2', '2001'
'2', '3', '2001'

...so表示，学生2是2000年最热情的学生，有4门课程**，学生1是2001年最热情的学生，有4门课程。

'1', '4', '2001'
'2', '4', '2000'

我终于开始工作的查询是：

Select * from (
   select max(coursecount), year from
    (select student_no, count(course_no) as coursecount, year
      from students
      group by student_no, year) as internal group by year) as maxes,
        (select student_no, count(course_no) as coursecount, year
          from students
          group by student_no, year) as students
 where maxes.coursecount = students.coursecount
 and maxes.year = students.year

...but，我有种感觉，有一个更好的方法来做这件事。

有人能告诉我一种更优雅的方法吗?用ANSI SQL和2)用Oracle中的分析函数做这件事？

提前谢谢你的帮助。

Answer 1

假设您使用的是Server 2005+

你可以试试

;WITH Vals AS (
    SELECT year, student_no, COUNT(course_no) Cnt
    FROM students
    GROUP BY  year, student_no
 )
, RowIDS AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY year ORDER BY CNT DESC) RowID
    FROM Vals
)
  SELECT *
  FROM RowIDS 
  WHERE RowID = 1

演示

Answer 2

在标准SQL中，您可以尝试：

select year,student_no
from students
group by year,student_no
having count(*) = (
   select max( cnt ) 
   from (
     select count(*) cnt
     from students
     group by year,student_no
   ) x
  )

演示：http://sqlfiddle.com/#!11/41d12/7