知方可补不足~row_number,rank,dense_rank,ntile排名函数的用法

  1. 云栖社区>
  2. 博客>
  3. 正文

知方可补不足~row_number,rank,dense_rank,ntile排名函数的用法

mcy247 2017-12-07 22:27:00 浏览1026
展开阅读全文

这篇文章介绍SQL中4个很有意思的函数,我称它的行标函数,它们是row_number,rank,dense_rank和ntile,下面分别进行介绍。

一  row_number:它为数据表加一个叫“行标示”的列,它在数据表中是连续的,我们必须按着某个顺序把表排序之后,才能使用row_number,看下列例子:

SELECT   row_number() OVER ( ORDER BY SalePrice ) AS row ,
                    *
          FROM      Product

结果表被加上了行号:

这个row_number在平常用的最多,它可以用来实现数据表的分页功能,看下面代码

 SELECT *
 FROM   ( SELECT    row_number() OVER ( ORDER BY SalePrice ) AS row ,
                    *
          FROM      Product
        ) AS Result
 WHERE  Result.row BETWEEN 1 AND 2 
 

它的含义是从第一条记录开始,取出2条记录,如果你想一页显示10条记录,可以使用BETWEEN 1 AND 10,如果想得到第二次的10条,那条件就变成BETWEEN 11 AND 20

二   rank:类型于row_number,不同之处在于,它会对order by 的字段进行处理,如果这个字段值相同,那么,行号保持不变,如代码:

 SELECT RANK() OVER ( ORDER BY SalePrice ) AS row ,
        SalePrice ,
        ProductID ,
        ProductName
 FROM   Product

结果如下:

三   dense_rank:与rank类型,不同之处在于行号是否保留一个位置,rank对保留这个位置,即上面图中,row的值由1直接变为3,因为它的1出现了两次,所以为2保留了一个位置,而dense_rank不会保留2这个位置,即实现的行号2其实是排在了第3位,如代码:

 SELECT DENSE_RANK() OVER ( ORDER BY SalePrice ) AS row ,
        SalePrice ,
        ProductID ,
        ProductName
 FROM   Product

结果如下:

四   ntile:为装桶操作,ntile(桶数)它在运行之前,先确定产生的桶数,然后根据桶数去生成行标,如代码:

 SELECT NTILE(1) OVER ( ORDER BY SalePrice ) AS ntile ,
        SalePrice ,
        ProductID ,
        ProductName
 FROM   Product

结果如下:

如果设为NTILE(3),那结果就为:

当我们面对一个复杂的问题时,考验的不是你是否能解决,而是你采取哪种方式去解决以及代码的性能问题。

本文转自博客园张占岭(仓储大叔)的博客,原文链接:知方可补不足~row_number,rank,dense_rank,ntile排名函数的用法,如需转载请自行联系原博主。

网友评论

登录后评论
0/500
评论
mcy247
+ 关注