sql-server-2005 - 使用 Row_Number() - 查找最大注册

Question

我想使用 row_number() 函数找出每门类(class)的最大试卷注册数。

注册表

CourseID  StudentName  PaperCode

 101    David   10001
 101    David   20000
 101    George  10000
 102    Peter   10000
 102    Peter   20000
 102    David   20000

预期的结果可能是

CourseID| StudentName | NumberofRegistration

101       David     2
102      Peter       2   

我尝试了以下查询，但无法扩展它以获得所需的结果。

select 

         CourseID,
         StudentName,
         NumberOfRegistration
from

(
  select 
         CourseID,
         StudentName,
         ROW_NUMBER() 
         over( 
              partition by count(papercode)
              order by CourseID asc) as NumberOfRegistration

来自 登记 按 CourseID,StudentName 分组 )x

请帮我完成它。

Answer 1

declare @T table
(
  CourseID int,
  StudentName varchar(10),
  PaperCode int
)

insert into @T values
(101,    'David',   10001),
(101,    'David',   20000),
(101,    'George',  10000),
(102,    'Peter',   10000),
(102,    'Peter',   20000),
(102,    'David',   20000)

select CourseID, StudentName, NumberofRegistration
from
  (
    select CourseID, StudentName, NumberofRegistration,
           row_number() over(partition by CourseID order by NumberofRegistration desc) as rn
    from
      (
        select CourseID, StudentName, count(*) as NumberofRegistration
        from @T
        group by CourseID, StudentName
      ) as T
  ) as T
where rn = 1