T-SQL排名函数

时间:2022-09-03 10:09:48

提到排名函数我们首先可能想到的是order by,这个是排序,不是排名,排名需要在前面加个名次序号的,order by是没有这个功能的。还可能会想到identity(1,1),它也给了一个序号,但是不能保证给出的序号是连续升序的。除非能够保证所有的Insert语句都能够正确成功地完成,并且没有删除操作,实际的使用中大多数的表都不能保证这样。

好在SQL Server中提供了一些排名函数来辅助实现这些功能。排名函数按照需要的顺序对数据进行排名,并提供一个值对数据。下面来了解一下这些排序函数功能。

  

ROW_NUMBER

 

ROW_NUMBER函数允许以上升,连续的顺序给每一行数据一个序号,注意ROW_NUMBER()后面一定要跟着over子句。来看语句:

use AdventureWorks
select
ROW_NUMBER() over(orderby LastName) as RowNum,
FirstName+''+ LastName as FullName
from HumanResources.vEmployee
where JobTitle='Production Technician - WC60'

这个语句对符合条件(JobTitle='Production Technician - WC60')的LastName按照升序排列,并加上排序的序号,这个序号是连续上升的。结果如下图1是部分结果。

T-SQL排名函数

图1

我们可以看到第一个人的LastName是Abercrombie,第二个人的LastName是Adams,以次类推。

  

PARTITION

 

如果我们想再细分一下,在一个小的分组范围内排序该怎么办呢?就是说让LastName以‘A’开头的作为第一组,在这个组内进行排序。以‘B’开头的作为第二组,在这个组内排序。以‘C’开头的作为第三组,在这个组内进行排序,如此等等。这里有一个很简单的实际例子,假如上面这些人都来参加同一场马拉松比赛,其中有男子组,女子组,男子残疾组,女子残疾组,60岁以上组等等。不管参赛者以第几位触线,名次都以他们的小组为基准。

可以通过PARTITION BY选项来重新排序,给数据分区或者数据区域唯一的递增序号。来看下面的语句:

[注] partition n. 划分,分开;[数] 分割;隔墙;隔离物;vt. [数] 分割;分隔;区分

select
ROW_NUMBER() over(PARTITION bysubstring(LastName,1,1) orderby LastName) as RowNum,
FirstName+''+ LastName as FullName
from HumanResources.vEmployee
where JobTitle='Production Technician - WC60'

这里模拟上面的情况,首先以Last Name的第一个字母作为分组,然后以第二个字母以后的字母来分组排序。来看看结果,如图2

T-SQL排名函数

图2

假设LastName以‘A’开头的是男子组,这个组有共有三个人,Kim Abercrombie是冠军,Jay Adams是亚军,Nancy Anderson是季军。假设LastName以‘B’开头的是女子组,这个组只有一个人Bryan Baker,无论如何她都是冠军。等等如此类推。这样一眼就能看出他们的小组名次了。

这里你可能会觉得使用order by一样可以得到这样类似的结果。如下代码:

 select
FirstName+''+ LastName as FullName
from HumanResources.vEmployee
where JobTitle='Production Technician - WC60'
orderbysubstring(LastName,1,1) ,LastName

这个把order by放在最后,排序放在最后,首先按照LastName的首字母排序,再按照剩整个LastName排序,结果如下图3

T-SQL排名函数

图3

结果和上面大致相同,可是少了前面的名次序号。于是我又对她进行了修改,代码如下:

select
ROW_NUMBER() over(orderbysubstring(LastName,1,1),LastName) as RowNum,
FirstName+''+ LastName as FullName
from HumanResources.vEmployee
where JobTitle='Production Technician - WC60'
这个和上面的类似,在排名函数中使用order by,并且是按照多个字段排序。来看看结果如图4
T-SQL排名函数
图4

排序没有错误,是我们想要的分组排序,但是前面的名次没有分组区分,和图1没有什么差别。可见图3和图4的做法完全是多余,纯属臆造,其实只要order by LastName都能得到正确的排序,只有partition by才是正解。通过上面的例子也可以对排序,排名这二者之间的区别有一个认识,他们虽然有相似之处,但是排名始终会产生一个名次序号,排序只要得到正确的顺序就好。

RANK

 

还是拿马拉松比赛来说事,如果有同时撞线的情况发生应该怎么计名次呢?例如A第一个撞线,B和C同时第二个撞线,D第三个撞线,如果我们想把D的名次计为第4名应该怎么处理呢?就是说不计顺序名次,只计人数。这时就可以使用RANK函数了。

[注] rank n. 等级;队列;排;军衔vt. 排列;把…分等vi. 列队;列为

在order by子句中定义的列上,如果返回一行数据与另一行具有相同的值,rank函数将给这些行赋予相同的排名数值。在排名的过程中,保持一个内部计数值,当值有所改变时,排名序号将有一个跳跃。

来看下面的语句:

 select
 ROW_NUMBER() over(orderby Department) as RowNum,
 RANK() over(orderby Department) as Ranking,
 FirstName+''+ LastName as FullName,
 Department
 from HumanResources.vEmployeeDepartment
 orderby RowNum

rank()函数右面也要跟上一个over子句。为了看到效果我们以Department作为排序字段,可以看到RowNum作为升序连续排名,Ranking作为计同排名,当Department的值相同时,Ranking中的值保持不变,当Ranking中的值发生变化时,Ranking列中的值将跳跃到正确的排名数值。来看结果:

T-SQL排名函数

图5

从这个结果中我们可以说这次马拉松赛跑的排名是:Tengiz Kharatishvili,Zainal Arifin,Sean Chai,Karen Berge,Chris Norred并列第1,Michael Sullivan,Sharon Salavaria,Roberto Tamburello,Gail Erickson,Jossef Goldberg并列第6,如此等等。

DENSE_RANK

在上面的例子中,A第一个撞线,B和C同时第二个撞线,D第三个撞线,如果我们想把B和C的名次计位第2名,D的名次计为第3名应该怎么处理呢?就是说考虑并列名次。这里使用DENSE_RANK函数,来看下面的代码。

 select
 ROW_NUMBER() over(orderby Department) as RowNum,
 DENSE_RANK() over(orderby Department) as Ranking,
 FirstName+''+ LastName as FullName,
 Department
 from HumanResources.vEmployeeDepartment
 orderby RowNum
 

结果如下:

T-SQL排名函数

图6

按照这个结果,我们可以说这次马拉松赛跑的排名是:Tengiz Kharatishvili,Zainal Arifin,Sean Chai,Karen Berge,Chris Norred并列第1,Michael Sullivan,Sharon Salavaria,Roberto Tamburello,Gail Erickson,Jossef Goldberg,Terri Duffy并列第2,等等如此。

NTILE

在开始这个之前,先来一段小插曲。梭罗是铅笔的发明者,不过他没有申请专利。据说他天赋异禀,在父亲的铅笔厂里面打包铅笔的时候,从一堆铅笔里面抓取一把,每次都能精确地抓到一打12支。他在森林中目测两颗树之间的距离,和护林员用卷尺测量的结果相差无几。现在如果我们想从一张表中抓取多比数据,每一笔都是相同的数目,并且标明第几组该怎么办呢?NTILE函数提供了这个功能,他能。来看代码:

 select
NTILE(30) over(orderby Department) as NTiles,
FirstName+''+ LastName as FullName,
Department
from HumanResources.vEmployeeDepartment

现在我们要抓取30个组的数据,并保证尽可能的保证每组数目相同。结果如下,

T-SQL排名函数

图7

这个视图*290条数据,290/30=9.7约等于10,所以每组10条数据,如图每一条数据都有一个组号。这个结果要比索罗精确。

T-SQL排名函数的更多相关文章

  1. sql排名函数--四个

    1 row_number 2 rank 3 dense_rank 4 ntile 例子如下: select * into #MyTablefrom(select '语文' as 课程,70 as 成绩 ...

  2. 你真的会玩SQL吗?表表达式,排名函数

    你真的会玩SQL吗?系列目录 你真的会玩SQL吗?之逻辑查询处理阶段 你真的会玩SQL吗?和平大使 内连接.外连接 你真的会玩SQL吗?三范式.数据完整性 你真的会玩SQL吗?查询指定节点及其所有父节 ...

  3. sql server 排名函数:DENSE_RANK

    一.需求 之前sql server 的排名函数用得最多的应该是RoW_NUMBER()了,我通常用ROW_NUMBER() + CTE 来实现分页:今天逛园,看到另一个内置排名函数还不错,自己顺便想了 ...

  4. SQL Server 排名函数实现

    在SQL Server 中有四大排名函数分别是: 1.row_number() 2.ntile() 3.rank() 4.dense_rank() -------------------------- ...

  5. SQL点滴20—T-SQL中的排名函数

    原文:SQL点滴20-T-SQL中的排名函数 提到排名函数我们首先可能想到的是order by,这个是排序,不是排名,排名需要在前面加个名次序号的,order by是没有这个功能的.还可能会想到ide ...

  6. Sql 四大排名函数(ROW_NUMBER、RANK、DENSE_RANK、NTILE)简介

    排名函数是Sql Server2005新增的功能,下面简单介绍一下他们各自的用法和区别.我们新建一张Order表并添加一些初始数据方便我们查看效果. CREATE TABLE [dbo].[Order ...

  7. SQL中的排名函数(ROW_NUMBER、RANK、DENSE_RANK、NTILE)简介

    排名函数是Sql Server2005新增的功能,下面简单介绍一下他们各自的用法和区别. 在使用排名函数的时候需要注意以下三点: 1.排名函数必须有 OVER 子句. 2.排名函数必须有包含 ORDE ...

  8. SQL Server 分析函数和排名函数

    分析函数基于分组,计算分组内数据的聚合值,经常会和窗口函数OVER()一起使用,使用分析函数可以很方便地计算同比和环比,获得中位数,获得分组的最大值和最小值.分析函数和聚合函数不同,不需要GROUP ...

  9. SQL Server进阶(八)查询——开窗函数、四大排名函数、透视数据、逆透视数据

    概述 ROW_NUMBER() OVER(PARTITION BY CustId ORDER BY ID DESC) https://www.jb51.net/article/75533.htm 开窗 ...

  10. Sql 四大排名函数(ROW_NUMBER、RANK、DENSE_RANK、NTILE)(转载)

    Sql 四大排名函数(ROW_NUMBER.RANK.DENSE_RANK.NTILE)简介   排名函数是Sql Server2005新增的功能,下面简单介绍一下他们各自的用法和区别.我们新建一张O ...

随机推荐

  1. 将类似 12.56MB 36.89KB 转成 以K为单位的数字【备忘】

    select case RIGHT(RESOURCE_SIZE,2) when 'MB' THEN SUBSTRING_INDEX(RESOURCE_SIZE,'MB',1)*1024 ELSE SU ...

  2. hibernate学习(设计多对多 关系 映射)

    // package org.crazy.app.domain; import java.util.HashSet; import java.util.Set; import javax.persis ...

  3. linux 进入mysql

    mysql -u root -ppassword show databases; \h 帮助 \q 退出

  4. a++累加

    <!DOCTYPE html><html lang="zh-CN"><head> <meta charset="UTF-8&qu ...

  5. Cidr计算

    项目上有个需求,要求计算cidr,网上perl和php,python很多,java的很少,呵呵,虽说懂点那个,毕竟还是会这个. 所以查了一下: https://foo.cs.ucsb.edu/cs56 ...

  6. 作为函数的mixin

    作为函数的mixin 在一个 mixin 内部定义的变量或 mixin,都调用者可见,因此,它们可以作为它的返回值.如,以下Less代码: .count(@x, @y) {     @sum:(@x ...

  7. linux 内核提权

    不经意间找到了大牛总结的一些Linux提权exp 我直接借花献佛分享给大家 #CVE #Description #Kernels CVE-2017-1000367 [Sudo] (Sudo 1.8.6 ...

  8. localStorage sessionStorage 用法

    sessionStorage.getItem('key') // 获取 sessionStorage.setItem('key','value') //设置 sessionStorage.remove ...

  9. Alpha冲刺随笔二:第二天

    课程名称:软件工程1916|W(福州大学) 作业要求:项目Alpha冲刺(十天冲刺) 团队名称:葫芦娃队 作业目标:在十天冲刺里对每天的任务进行总结. 随笔汇总:https://www.cnblogs ...

  10. &lbrack;Axure RP&rsqb; – 鼠标滑入按钮时自动下拉表单的设计示例

    转:http://blog.qdac.cc/?p=2197 Axure RP 是个好东东呀,大大方便了程序员与客户之间的前期调研时的交流.不过有一些控制并没有鼠标移入和移出的操作,比如 HTML 按钮 ...