一、sqlserver 排序 rank()函数
使用方法:和row_number() over(order by 字段名称 desc) 使用方法类似
eg: select rank() over(order by age desc) from students ( styudent 表里面有字段 int 类型age字段 ) 如果有2个相同的age值,则并列
例子SELECT RANK() OVER (ORDER BY Age) AS [Rank by Age],
FirstName,
Age
FROM Person输出如下:Rank by Age FirstName Age
-------------------- ---------- -----------
1 Larry 5
2 Doris 6
2 George 6
4 Mary 11
4 Sherry 11
6 Sam 17
7 Ted 23
7 Marty 23
9 Sue 29
10 Frank 38
11 John 40
看到了么,同年岭的话,将有相同的顺序,顺序成1,2,2,4了。
二、ntile( )函数
SELECT FirstName,
Age,
NTILE(3) OVER (ORDER BY Age) AS [Age Groups]
FROM Person
输出结果:
FirstName Age Age Groups
---------- ----------- --------------------
Larry 5 1
Doris 6 1
George 6 1
Mary 11 1
Sherry 11 2
Sam 17 2
Ted 23 2
Marty 23 2
Sue 29 3
Frank 38 3
John 40 3
这个函数按照ntile(n)中的N,把记录强制分成多少段,11条记录现在分成3段了,lary到mary是第1段,sherry到maty是第2段,sue到john是第3段 了。
三、DENSE_RANK( )函数
SELECT DENSE_RANK() OVER (ORDER BY Age) AS [Dense Rank by Age],
FirstName,
Age
FROM Person
输出结果为:
Dense Rank by Age FirstName Age
-------------------- ---------- -----------
1 Larry 5
2 Doris 6
2 George 6
3 Mary 11
3 Sherry 11
4 Sam 17
5 Ted 23
5 Marty 23
6 Sue 29
7 Frank 38
8 John 40
看到了么,和rank函数区别是,顺序始终是连续的,Doris 和George同年,都是排第2位,但之后的mary不象rank函数那样排第4,而是排第3位了