SQL Server 2005 T-SQL 学习笔记：排名函数

2005中共有四个排名函数：Row_Number, Rank(), Dense_Rank(), NTILE()

Row_Number()：
按一个排序表达式，将查询结果编号，主要用于分页中使用.

Row_Number()简单示例：

 

select SalesOrderID, OrderDate, CustomerID, SalesPersonID,
row_number() over (order by CustomerID) as RowNumber
from Sales.SalesOrderHeader
where SalesPersonID is not null
Order By CustomerID desc 
--查询结果的排序可以不同于row_number中的排序，这时RowNumber列将被打乱

结果:

SalesOrderID OrderDate               CustomerID  SalesPersonID RowNumber
------------ ----------------------- ----------- ------------- --------------------
50735        2003-06-01 00:00:00.000 701         285           3801
48352        2002-12-01 00:00:00.000 701         285           3802
49469        2003-03-01 00:00:00.000 701         285           3803
71843        2004-06-01 00:00:00.000 701         285           3804
53529        2003-09-01 00:00:00.000 701         285           3805
59043        2003-12-01 00:00:00.000 701         285           3806
63181        2004-02-01 00:00:00.000 700         279           3797
... ...

Row_Number中可以实现按一列的值不同而重新编号，这时在order by 前面加入Partition by [列名]

Row_Number中的分区示例:

 

select SalesOrderID, OrderDate, CustomerID, SalesPersonID,
row_number() over (partition by SalesPersonID order by CustomerID) as RowNumber
from Sales.SalesOrderHeader
where SalesPersonID is not null and SalesOrderID > 65000

结果：

SalesOrderID OrderDate               CustomerID  SalesPersonID RowNumber
------------ ----------------------- ----------- ------------- --------------------
71919        2004-06-01 00:00:00.000 38          268           1
65298        2004-03-01 00:00:00.000 116         268           2
67286        2004-04-01 00:00:00.000 120         268           3
71779        2004-06-01 00:00:00.000 149         268           4
69528        2004-05-01 00:00:00.000 309         268           5
69545        2004-05-01 00:00:00.000 436         268           6
65294        2004-03-01 00:00:00.000 489         268           7
67277        2004-04-01 00:00:00.000 530         268           8
65310        2004-03-01 00:00:00.000 3           275           1
71889        2004-06-01 00:00:00.000 3           275           2
67292        2004-04-01 00:00:00.000 4           275           3
65315        2004-03-01 00:00:00.000 21          275           4
... ...

Rank():
用于对一列（多列也行)的值进行排名，有并列名次的，后面的名次会加上并列的次数．

Rank简单示例：

select SalesOrderID, OrderDate, CustomerID, SalesPersonID,
Rank() over ( order by CustomerID, SalesPersonID ) as Rank
from Sales.SalesOrderHeader
where SalesPersonID is not null and SalesOrderID > 10000

结果
SalesOrderID OrderDate               CustomerID  SalesPersonID Rank
------------ ----------------------- ----------- ------------- --------------------
43860        2001-08-01 00:00:00.000 1           280           1
44501        2001-11-01 00:00:00.000 1           280           1
45283        2002-02-01 00:00:00.000 1           280           1
46042        2002-05-01 00:00:00.000 1           280           1
46976        2002-08-01 00:00:00.000 2           283           5
47997        2002-11-01 00:00:00.000 2           283           5
49054        2003-02-01 00:00:00.000 2           283           5
50216        2003-05-01 00:00:00.000 2           283           5
51728        2003-08-01 00:00:00.000 2           283           5
57044        2003-11-01 00:00:00.000 2           283           5
63198        2004-02-01 00:00:00.000 2           283           5
69488        2004-05-01 00:00:00.000 2           283           5
71889        2004-06-01 00:00:00.000 3           275           13
... ...

和Row_Number一样，Rank也可以使用Partition By

Dense_Rank():
和Rank很像，只不过并列名次的，后面的名次连续显示．

Dense_Rank简单示例:

select SalesOrderID, OrderDate, CustomerID, SalesPersonID,
Dense_Rank() over ( order by CustomerID, SalesPersonID ) as Dense_Rank
from Sales.SalesOrderHeader
where SalesPersonID is not null and SalesOrderID > 10000

结果
SalesOrderID OrderDate               CustomerID  SalesPersonID Dense_Rank
------------ ----------------------- ----------- ------------- --------------------
43860        2001-08-01 00:00:00.000 1           280           1
44501        2001-11-01 00:00:00.000 1           280           1
45283        2002-02-01 00:00:00.000 1           280           1
46042        2002-05-01 00:00:00.000 1           280           1
46976        2002-08-01 00:00:00.000 2           283           2
47997        2002-11-01 00:00:00.000 2           283           2
49054        2003-02-01 00:00:00.000 2           283           2
50216        2003-05-01 00:00:00.000 2           283           2
51728        2003-08-01 00:00:00.000 2           283           2
57044        2003-11-01 00:00:00.000 2           283           2
63198        2004-02-01 00:00:00.000 2           283           2
69488        2004-05-01 00:00:00.000 2           283           2
71889        2004-06-01 00:00:00.000 3           275           3
... ...

NTILE(N):
给定一个总名次N，将查询结果按指定的条件排名次，并列时，名次连续.

NTILE的分区示例：

select SalesOrderID, OrderDate, CustomerID, SalesPersonID,
NTILE(10) over ( partition by SalesPersonID order by CustomerID) as NTILE
from Sales.SalesOrderHeader
where SalesPersonID is not null and SalesOrderID > 60000

结果
SalesOrderID OrderDate               CustomerID  SalesPersonID NTILE
------------ ----------------------- ----------- ------------- --------------------
71919        2004-06-01 00:00:00.000 38          268           1
65298        2004-03-01 00:00:00.000 116         268           2
67286        2004-04-01 00:00:00.000 120         268           3
71779        2004-06-01 00:00:00.000 149         268           4
61200        2004-01-01 00:00:00.000 205         268           5
69528        2004-05-01 00:00:00.000 309         268           6
61203        2004-01-01 00:00:00.000 421         268           7
69545        2004-05-01 00:00:00.000 436         268           8
65294        2004-03-01 00:00:00.000 489         268           9
67277        2004-04-01 00:00:00.000 530         268           10
65310        2004-03-01 00:00:00.000 3           275           1
71889        2004-06-01 00:00:00.000 3           275           1
67292        2004-04-01 00:00:00.000 4           275           1
61193        2004-01-01 00:00:00.000 4           275           1
65315        2004-03-01 00:00:00.000 21          275           1
71881        2004-06-01 00:00:00.000 21          275           1
69495        2004-05-01 00:00:00.000 22          275           1
63162        2004-02-01 00:00:00.000 22          275           1
65301        2004-03-01 00:00:00.000 39          275           1
71912        2004-06-01 00:00:00.000 39          275           2
... ...