请选择 进入手机版 | 继续访问电脑版
贠老师培训群:150322713    贠老师QQ:767708506

贠老师office培训-excel学习网

 找回密码
 立即注册
点击咨询贠老师
查看: 180|回复: 6

SQL Server 排名函数

[复制链接]

607

主题

604

帖子

1909

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
1909
发表于 2016-1-8 16:32:27 | 显示全部楼层 |阅读模式

一、概述

1、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

本文出自 “我们一起追过的MSSQL” 博客,请务必保留此出处http://jimshu.blog.51cto.com/3171847/1176067


回复

使用道具 举报

0

主题

766

帖子

772

积分

高级会员

Rank: 4

积分
772
发表于 2016-6-5 11:39:16 | 显示全部楼层
先收藏再学习,谢谢啦!
回复 支持 反对

使用道具 举报

0

主题

3

帖子

26

积分

新手上路

Rank: 1

积分
26
发表于 2016-6-14 14:33:22 | 显示全部楼层
顶!!!!!!!!!!
回复

使用道具 举报

2

主题

1067

帖子

156

积分

注册会员

Rank: 2

积分
156
发表于 2016-8-2 10:14:09 | 显示全部楼层
赞赞
回复 支持 反对

使用道具 举报

0

主题

775

帖子

783

积分

高级会员

Rank: 4

积分
783
发表于 2016-8-13 22:48:03 | 显示全部楼层
请问大家有什么看法?我绝对喜欢这个帖子
回复 支持 反对

使用道具 举报

8

主题

1035

帖子

88

积分

注册会员

Rank: 2

积分
88
发表于 2018-8-27 11:01:04 | 显示全部楼层
谢谢您的分享!
回复 支持 反对

使用道具 举报

2

主题

1067

帖子

156

积分

注册会员

Rank: 2

积分
156
发表于 2018-12-8 09:30:20 | 显示全部楼层
还有许多问题不明白,有点恼火啊!
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则



陕ICP备15003731号  

贠老师培训 GMT+8, 2018-12-19 22:26 , Processed in 0.223990 second(s), 26 queries .

快速回复 返回顶部 返回列表