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