善用SQL排名函数，让您的查询飞的更精彩 _SQL

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

文章插图

今天我们就谈一个会让我们头疼的问题：如何对查询的结果进行排名。没错，是排名，不是排序，跟Order By有点关系，但可以认为是另一个问题。排名函数不但可以实现排序，还能够生成排序的排名序列。
演示数据准备【善用SQL排名函数，让您的查询飞的更精彩】今天我们就以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)+1and@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;运行效果如下图所示：