一、概述

１、RANK

　　RANK返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。在排名中出现并列排名的情况时，会出现间断（跳跃）。

　　语法：ROW_NUMBER ( )     OVER ( [ <partition_by子句> ] <order_by子句> ) 

　　< partition_by子句> 将 FROM 子句生成的结果集划分成 RANK 函数适用的分区。< order_by子句>确定将 RANK 值应用于分区中的行时所基于的顺序。当在排名函数中使用 <order_by子句> 时，不能用整数表示列。

 

2、DENSE_RANK

　　DENSE_RANK返回结果集分区中行的排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。

　　语法：DENSE_RANK ( )    OVER ( [ <partition_by子句> ] < order_by子句> )

　　<partition_by子句>将 FROM 子句生成的结果集划分为数个应用 DENSE_RANK 函数的分区。<order_by子句>确定将 DENSE_RANK 值应用于分区中各行的顺序。整数不能表示排名函数中使用的 <order_by子句> 中的列。 

 

3、NTILE

　　NTILE 将有序分区中的行分发到指定数目的组中。各个组有编号，编号从一开始。对于每一个行，NTILE 将返回此行所属的组的编号。

　　语法：NTILE (正整数常量表达式)    OVER ( [ <partition_by子句> ] < order_by子句> )

　　正整数常量表达式，用于指定每个分区必须被划分成的组数，类型可以为 int 或 bigint。<partition_by子句>将 FROM 子句生成的结果集划分成 RANK 函数适用的分区。<order_by子句>确定 NTILE 值分配到分区中各行的顺序。当在排名函数中使用 <order_by子句> 时，不能用整数表示列。

 

4、ROW_NUMBER 

　　ROW_NUMBER返回结果集分区内行的序列号，每个分区的第一行从 1 开始。

　　语法：ROW_NUMBER ( )     OVER ( [ <partition_by子句> ] <order_by子句> )

　　<partition_by子句> 将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。<order_by子句>确定将 ROW_NUMBER 值分配给分区中的行的顺序。当在排名函数中使用 <order_by子句> 时，不能用整数表示列。

 

 

二、构建测试环境

CREATE TABLE [dbo].[StudentMarks](

    [StudentCode] [varchar](10) NOT NULL,
    [SubjectCode] [varchar](10) NOT NULL,
    [Marks] [tinyint] NOT NULL,
    CONSTRAINT [PK_StudentMarks] PRIMARY KEY CLUSTERED
    ([StudentCode] ASC, [SubjectCode] ASC) ON [PRIMARY]
) ON [PRIMARY]

declare @x tinyint,@y tinyint
set @x=0
while @x<10
begin
  set @y=0
  while @y<5
  begin
    insert into dbo.StudentMarks
    values
    ( substring('DavidGarryJae  Jill LindaLynn Mike Rose Shu  Tete ' , @x*5+1 , 5),
    substring('BasicVB   VC   PB   PHP ',@y*5+1 , 5),  
    cast(floor(rand()*100) as tinyint))
    set @y = @y + 1
  end
  set @x = @x + 1
end

 

三、示例

1、对学生的“VB”这门课程的成绩进行排名。遇到一位或多位学生的成绩相同的情况，则给这些学生使用相同的排名，再往后的排名是不连续的（跳跃）。

SELECT StudentCode ,Marks,

  RANK() OVER(ORDER BY Marks DESC) AS RankValue
FROM StudentMarks
WHERE SubjectCode='VB'

　　结果：

StudentCode  Marks  RankValue

Mike    97   1
Shu     97   1
Tete    94   3
Rose    94   3
Garry   90   5
Lynn    57   6
Linda   53   7
David   29   8
Jae     26   9
Jill    18   10

 

2、统计每位学生的5门课程的平均成绩，再根据平均成绩进行排名。

SELECT StudentCode ,AVG (Marks) as AvgMark,

  RANK() OVER(ORDER BY AVG (Marks) DESC) AS RankValue
FROM StudentMarks
GROUP BY StudentCode

　　结果：

StudentCode  AvgMark  RankValue

Garry   65   1
Tete    58   2
Rose    56   3
Mike    54   4
David   53   5
Shu     50   6
Lynn    49   7
Linda   45   8
Jill    38   9
Jae     35   10 

 

3、按单科成绩进行排名，按排名顺序显示结果。

SELECT StudentCode, SubjectCode, Marks ,

    RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS RankValue
  FROM StudentMarks
  order by RankValue,SubjectCode,StudentCode

　　结果（为节省篇幅，此处仅摘录前7条记录）：

StudentCode SubjectCode Marks RankValue

David Basic 93   1
Mike  PB    66   1
Rose  PHP   90   1
Mike  VB    97   1
Shu   VB    97   1
Lynn  VC    84   1
Shu   Basic 86   2
。。。。。。

注意，一共是5门课程，但是排第1名的却有6位学生。这是因为“VB”这门课程出现了并列第一。

 

4、从上例的结果中，仅筛选每门课程排第一的学生。

SELECT StudentCode , SubjectCode, Marks ,RankValue  FROM (

  SELECT StudentCode, SubjectCode, Marks ,
    RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS RankValue
  FROM StudentMarks ) tmp  
WHERE  RankValue = 1

　　结果：

StudentCode SubjectCode Marks RankValue

David Basic 93   1
Mike  PB    66   1
Rose  PHP   90   1
Mike  VB    97   1
Shu   VB    97   1
Lynn  VC    84   1

 

5、统计每位学生的5门课程的平均成绩，然后将学生为分2部分，平均成绩在前半部分（前5名）的排名分数为1，平均成绩在后半部分（后5名）的排名分数为2。

SELECT StudentCode, AVG(Marks)  as AvgMark,

  NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS RankValue
FROM StudentMarks
GROUP BY StudentCode
ORDER BY AvgMark DESC 

　　结果：

StudentCode  AvgMark  RankValue

Garry   65   1
Tete    58   1
Rose    56   1
Mike    54   1
David   53   1
Shu     50   2
Lynn    49   2
Linda   45   2
Jill    38   2
Jae     35   2