sql 除以_善用SQL排名函数，让您的查询飞的更精彩

经常写SQL脚本的朋友，对查询的多样化要求可能会经常头疼。数据库SQL的语法是固定的、但应用要求却是千差万别的。依靠我们所掌握的知识，大部分的查询需求我们还是有办法的解决的，但总有那么一些要求，把我们搞的非常被动。

今天我们就谈一个会让我们头疼的问题：如何对查询的结果进行排名。没错，是排名，不是排序，跟Order By有点关系，但可以认为是另一个问题。排名函数不但可以实现排序，还能够生成排序的排名序列。

演示数据准备

今天我们就以SQL Server为例，结合四大排名函数，详细讲解一下如何使用四大排名函数实现查询结果的排名。MySQL也有类似的排名函数，使用方法与SQL Server大同小异。

为了演示需要，我们需要先创建一个表变量作为销售表，记录不同区域、城市、年月的销售金额，然后再对表中的数据进行排名处理。演示数据创建脚本如下：

declare @sale table( FName nvarchar(50), FDistrict nvarchar(50), FYear smallint, FMonth smallint, FAmount decimal(28,10));insert into @salevalues('张三','北京',2019,4,20000),('张三','深圳',2019,4,40000),('李四','北京',2019,4,30000),('李四','深圳',2019,4,40000),('王二','北京',2019,4,70000),('王二','深圳',2019,4,60000),('马六','北京',2019,4,80000),('马六','深圳',2019,4,70000);

运行结果请参考下图：

有了演示数据，我们就把四个排名函数的应用和区别挨个理一理。

ROW_NUMBER，简单方便又强大

row_number是最常用、最简单的排名函数，其语法格式如下：

row_number() over(order by field列表 asc|desc)

语法格式看上去有点怪怪的，前半截row_number()是排名函数，紧接其后的over子句则是指定排序的规则。其它三个排名函数也有over子句，用途和语法也是一样的。整个函数作为一个整体，其返回值就是排名序列号，序列号从1开始依次累加。

比如我们要按照销售金额进行排名，语法格式如下：

select ROW_NUMBER() over(order by FAmount desc) as FRank,* from @sale;

运行效果参考下图：

通过上图的查询结果可以看出，FRank字段就是返回的排名字段。根据over子句，可以定义任何自己需要的排名规则。

如果您认为row_number函数只是能实现如此排名，那您就想的太简单了。row_number函数还有一个很重要的扩展用途，可以实现查询分页，我们举一个例子来说明如何使用row_number实现查询分页。

为了使演示效果更明显，我们使用公用表表达式返回排名序列值和数据，根据要求的每页行数和第几页，通过where子句限定排名序列的起点和终点。

declare @pagesize int =4;--每页记录数declare @pagenum int =1;--第几页with cte as( select row_number() over(order by FAmount desc) as FRank,* from @sale)select * from ctewhere FRank between  @pagesize*(@pagenum-1)+1  and  @pagenum*@pagesize;

运行效果如下图所示：

这种写法基本是通用的，您可以比葫芦画瓢稍加改造，就可以用在您的分页脚本中。

如果在查询中使用order by子句，要注意order by子句最好与排名中over子句的order by一致，如果不一致，可能导致返回排名序列是不连续的，但这并不影响数据页的正确性。

还有一种可以实现分页写法，使用offset进行分页，这里我就不再赘述了。如果您想要更进一步了解分页语法，可以参考我之前写过的文章《如何在SQLServer查询中实现高效分页》。

RANK，相同值共用排名值，跳着排

RANK函数与ROW_NUMBER函数的语法和效果类似，最大的区别在于，如果碰到相同的字段值，会使用相同的排名序列值，后续的序列值则会跳过共用序列值。

其语法格式如下：

rank() over(order by field列表 asc|desc)

下面我们就实战一下：

select RANK() over(order by FAmount desc) as FRank,* from @sale;

运行效果如下图所示：

我们从上图可以很明显看出来，第2、3条记录因值相同，使用了相同的序列值“2”，到了第四条，排名序列值直接使用了“4”而跳过了“3”。

DENSE_RANK，相同值共用排名值，接着排

DENSE_RANK函数与RANK函数的语法和效果类似，区别就像函数名称中的DENSE(紧密的)含义一样，如果碰到相同的字段值，虽然都会使用相同的排名序列值，但序列值是连续的。

其语法格式如下：

dense_rank() over(order by field列表 asc|desc)

下面我们就实战一下：

select DENSE_RANK() over(order by FAmount desc) as FRank,* from @sale;

运行效果如下图所示：

可以看出RANK和DENSE_RANK确实有共同点，但也有区别。RANK排名值如果碰到相同字段值，则会使用相同的排名值，后续会跳过断开使用新的排名值；DENSE_RANK碰到相同字段值同样使用相同的排名值，但是接着排下来，不会断开。

NTILE，根据约定分组

NTILE函数的语法和用途上面三个明显不同，其功能主要用来实现对记录进行分组。根据NTILE约定分组的组数，将查询结果按照over子句的排序标准进行分组，分组组号按照序列排列，同一个组使用同一个组号。

其语法格式如下：

ntile(组数) over(order by field列表 asc|desc)

如果指定的组数刚好实现均分，则每一组的记录数是相同的，比如我们将8条记录分成四组，则会有四个组，组号分别是1、2、3、4，每一组都会有两个记录，举例如下：

select NTILE(4) over(order by FAmount desc) as FRank,* from @sale;

运行效果如下：

如果分成五组，明显无法均分，那如何分呢？

当然是有一种逻辑存在的。这种逻辑可以这样理解，从第一组开始，使用总记录数除以组数，获取等于或最大于相除结果的最小整数，作为第一组的记录条数；剩余的记录条数按照相同算法依次类推。通俗来讲，NTITLE在优先配足靠前的分组的记录数的前提下，尽量进行均分。

下面我们我实战将以上记录分成五组的执行情况：

select NTILE(5) over(order by FAmount desc) as FRank,* from @sale;

运行效果如下：

从上图可以看出，记录条数是8条，分成5组，我们用8除以5，最接近且大于等于的整数是2，第一组占用了两条记录，这时候还剩余6条记录分4组；依次类推，6除以4，最接近且大于等于的整数是2，第二组还是2条记录；此时剩余4条记录要分3组，4除以3，最接近且大于等于的整数还是2；再往下，剩余两条分两组，这时候就刚好均分了。

需要明确的是，NTILE也可用来分页，但因分组逻辑的原因，效果就不如ROW_NUMBER理想了。

---

通过上述分析，我们对排名函数就有了充分的认识，您大可根据需要使用。希望对您有所帮助！