一、初始化数据:以用户成绩表为例
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 )
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
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
-- ------------------ -------------------------------- ---------------------------------------------------------------- -----------
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
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
-- ------------------ -------------------------------- ---------------------------------------------------------------- -----------
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
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
-- ------------------ -------------------------------- ---------------------------------------------------------------- -----------
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
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
-- ------------------ -------------------------------- ---------------------------------------------------------------- -----------
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
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
-- ------------------ -------------------------------- ---------------------------------------------------------------- -----------
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
-- ------------------ -------------------------------- ---------------------------------------------------------------- -----------
1 pd005 Evens 93
2 pd004 Brue 86
3 pd002 James 80
4 pd003 Allen 80
5 pd001 Steven 78