SQL Server 排名函数（row_number,rank,dense_rank,ntile）实例，详解

SQL Server2005新增的排名函数

 

1、ROW_NUMBER
        这是响尾蛇同学提到可以用来做分页的函数。它的主要用途是列出结果的编号。根据这个编号我们来做排序就会比较简单。所以我之前写的那个通用的分页存储过程，可以这样写：

-- =============================================
-- Author:  <张婷婷>
-- Create date: <2006-10-23>
-- Description: <仅适用于SQL Server2005的通用分页存储过程>
-- 感谢“大陆响尾蛇”提醒采用ROW_NUMBER函数
-- =============================================
Create PROCEDURE [dbo].[spCommonPageData]
 @Select NVARCHAR(500),   -- 要查询的列名，用逗号隔开（Select后面From前面的内容）
 @From NVARCHAR(200),   -- From后的内容
 @Where NVARCHAR(500) = NULL, -- Where后的内容
 @OrderBy NVARCHAR(100) = NULL, -- 排序字段
 @Key NVARCHAR(50),    -- 分页主键
 @Page INT,      -- 当前页 ***计数从1开始***
 @PageSize INT,     -- 每页大小
 @TotalCount INT OUTPUT,   -- 总记录数
 @Result TINYINT OUTPUT   -- 执行是否成功
AS
BEGIN
 SET NOCOUNT ON;

 Declare @Sql nVarchar(1000), @Sql2 NVARCHAR(500)

 Set @Sql=
'Select '+@Select+'
From (Select '+@Select+',ROW_NUMBER() OVER(ORDER BY '+@Key+') AS ROWNUMBER
        From '+@From+
        Case IsNull(@Where,'') When '' Then '' Else ' Where '+@Where End+')T
 Where ROWNUMBER Between '+Cast(@PageSize*(@Page-1) As Varchar(10))+' And '+Cast(@PageSize*@Page As Varchar(10))+
Case IsNull(' Order By '+@OrderBy,'') When '' Then '' Else
 ' Order By '+@OrderBy End

 Exec(@Sql)

 Set @Sql2='Select 1 As Count From '+@From+Case IsNull(@Where,'') When
  '' Then '' Else ' Where '+@Where End
 Exec(@Sql2)
 SET @TotalCount = @@ROWCOUNT
 
 SET @Result = 0
END

2、RANK
        这个函数是做不连续排名的，语法如下：
RANK OVER(ORDER BY COLUMN)
        比如说，Table1有一列Score，有5条记录：
SCORE
-----------
60
80
90
60
50
那么下面这条SQL语句

 

 

SELECT SCORE,RANK() OVER(ORDER BY SCORE DESC) AS RANK
FROM TABLE1

的结果应该是
SCORE       RANK
----------- --------------------
90          1
80          2
60          3
60          3
50          5

类似用group by 也可实现不连续排名

如：select a.Name,a.Sales,count(b.Sales) count_tank from #temp a,#temp b where a.Sales<=b.Sales
or (a.Sales=b.Sales and a.Name=b.Name) group by a.Name ,a.Sales
order by  a.Sales desc

3、DENSE_RANK
       这个函数是做连续排名的，语法如下：
DENSE_RANK OVER(ORDER BY COLUMN)
        和RANK的区别，可以从下面的SQL语句执行结果看到

 

 

SELECT SCORE,RANK() OVER(ORDER BY SCORE DESC) AS RANK
FROM TABLE1

的结果应该是

SCORE       RANK
----------- --------------------
90          1
80          2
60          3
60          3
50          4

4、NTILE
        这个函数是用来分组的，语法如下：
NTILE(N) OVER(ORDER BY COLUMN)
        选出来的是组号，下面这条SQL语句

SELECT SCORE,NTILE(2) OVER(ORDER BY SCORE) AS NTILE
FROM TALBE1

的结果如下：
SCORE       NTILE
----------- --------------------
90          1
80          1
60          1
60          2
50          2