SQL ROW_NUMBER() 的应用:分组之后再取前几行

  题目要求:  查询各科成绩前三名的记录

  有一下表,

  SC(S#,C#,Score)成绩表:S# 学号,C# 课程号,Score 分数.

  Course(C#,CName,T#) 课程:C# 课程号,CName 课程名称,T# 老师号.

 

   sql语句:

  Method1:

SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
      FROM SC t1
      WHERE score IN (SELECT TOP 3 score
              FROM SC
              WHERE t1.C#= C# 
            ORDER BY score DESC
              )
      ORDER BY t1.C#;

因为不同的课程号,可以对应相同的课程.按照这样的需求,上面的结果就有点问题.没有考虑到这种情况.

 

Method 2:

 

   SELECT S#,SCORE,CNAME FROM (
SELECT  SC.S#,SC.SCORE,CNAME,row_number() over (partition by CNAME ORDER BY SCORE DESC) AS ROWINDEX  FROM SC,COURSE C
WHERE SC.C#=C.C# 
GROUP BY SC.S#,SC.SCORE,CNAME) AS A
WHERE ROWINDEX<=3

 

这种方法考虑到了不同的课程号,对应相同的课程.其中用到了row_number 方法:

语法:

ROW_NUMBER()  OVER  ([<partition_by_clause>]  <order_by_clause>)

说明:返回结果集分区内行的序列号,每个分区的序列号从1开始.

备注: ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序.

参数:<partition_by_clause>:将FROM子句生长的结果集划入应用了ROW_NUMBER函数的分区.

       <order_by_clause>:确定将Row_Number值分配给分区中的行的顺序.

返回类型:bigint

 

----