SqlServer2005新增排序函数实例解释

一、初始化数据：以用户成绩表为例

create   database  demo

use  demo
create   table  T_User
(
      UserId  varchar ( 32 )  not   null   primary   key ,UserName  varchar ( 64 )  not   null ,Score  int   not   null
)

insert   into  T_User(UserId,UserName,Score)  values ( ' pd002 ' , ' Steven ' , 78 )
insert   into  T_User(UserId,UserName,Score)  values ( ' pd001 ' , ' James ' , 80 )
insert   into  T_User(UserId,UserName,Score)  values ( ' pd003 ' , ' Allen ' , 80 )
insert   into  T_User(UserId,UserName,Score)  values ( ' pd004 ' , ' Brue ' , 86 )
insert   into  T_User(UserId,UserName,Score)  values ( ' pd005 ' , ' Evens ' , 93 )

二、row_number()是通过对特定列来排序，比如成绩：

use  demo
select  row_number()  over ( order   by  Score  desc )  as  RowId, *
from  T_User

结果：

RowId                UserId                           UserName                                                         Score
-- ------------------ -------------------------------- ---------------------------------------------------------------- -----------
1                     pd005                            Evens                                                             93
2                     pd004                            Brue                                                              86
3                     pd002                            James                                                             80
4                     pd003                            Allen                                                             80
5                     pd001                            Steven                                                            78

三、rank()排序字段相同的记录占有名次

use  demo
select  rank()  over ( order   by  Score  desc )  as  RowId, *
from  T_User

结果：

RowId                UserId                           UserName                                                         Score
-- ------------------ -------------------------------- ---------------------------------------------------------------- -----------
1                     pd005                            Evens                                                             93
2                     pd004                            Brue                                                              86
3                     pd002                            James                                                             80
3                     pd003                            Allen                                                             80
5                     pd001                            Steven                                                            78

四、dense_rank() 排序字段相同的记录同占一个名次

use  demo
select  dense_rank()  over ( order   by  Score  desc )  as  RowId, *
from  T_User

结果：

RowId                UserId                           UserName                                                         Score
-- ------------------ -------------------------------- ---------------------------------------------------------------- -----------
1                     pd005                            Evens                                                             93
2                     pd004                            Brue                                                              86
3                     pd002                            James                                                             80
3                     pd003                            Allen                                                             80
4                     pd001                            Steven                                                            78

五、ntile() 是按排序字段把结果分成几个等级

use  demo
select  ntile( 3 )  over ( order   by  Score  desc )  as  RowId, *
from  T_User

结果：

RowId                UserId                           UserName                                                         Score
-- ------------------ -------------------------------- ---------------------------------------------------------------- -----------
1                     pd005                            Evens                                                             93
1                     pd004                            Brue                                                              86
2                     pd002                            James                                                             80
2                     pd003                            Allen                                                             80
3                     pd001                            Steven                                                            78

六、另外可以利用row_number()进行高效分页

-- @PageSize为每页数据多少，@PageIndex为当前页
use  demo

declare   @PageSize   int
set   @PageSize = 2
declare   @PageIndex   int
set   @PageIndex = 2

select   top ( @PageSize )  *
from
(
     select  row_number()  over ( order   by  Score  desc )  as  RowId, *
     from  T_User
) T_User_Score
where  RowId  between  (( @PageIndex   - 1 ) * @PageSize   + 1 )  and   @PageSize * @PageIndex

分页数据：

RowId                UserId                           UserName                                                         Score
-- ------------------ -------------------------------- ---------------------------------------------------------------- -----------
3                     pd002                            James                                                             80
4                     pd003                            Allen                                                             80

全部数据：

RowId                UserId                           UserName                                                         Score
-- ------------------ -------------------------------- ---------------------------------------------------------------- -----------
1                     pd005                            Evens                                                             93
2                     pd004                            Brue                                                              86
3                     pd002                            James                                                             80
4                     pd003                            Allen                                                             80
5                     pd001                            Steven                                                            78