<本blog内容全部原创,如有转载,请注明出处>

        首先感谢“大陆响尾蛇”对我上次写的《通用的分页存储过程》所做的评论。

        根据响尾蛇同学的提示,我又翻查了联机丛书,发现SQL Server2005新增的几个排名函数,非常具有实用意义。
1、ROW_NUMBER
        这是响尾蛇同学提到可以用来做分页的函数。它的主要用途是列出结果的编号。根据这个编号我们来做排序就会比较简单。所以我之前写的那个通用的分页存储过程,可以这样写:

-- =============================================
--
 Author:  <Tracy>
--
 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-1As 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 DESCAS RANK
FROM TABLE1


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

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

 

 

SELECT SCORE,RANK() OVER(ORDER BY SCORE DESCAS 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(2OVER(ORDER BY SCORE) AS NTILE
FROM TALBE1

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

posted on 2006-10-23 13:30  Tracy.Chuang  阅读(1069)  评论(4编辑  收藏  举报