MSSQL2005新增了四个排名函数,ROW_NUMBER, RANK, DENSE_RANK, NTILE。利用这些函数可以有效地分析数据以及向查询的结果行提供排序值。
建立测试数据,分析它们各自的作用。
3 | [StudentID] [ bigint ] NOT NULL , |
4 | [ClassID] [ bigint ] NOT NULL , |
5 | [TestScore] [ decimal ](4, 1) NOT NULL |
9 | INSERT INTO [Test] VALUES (100001,100,90) |
10 | INSERT INTO [Test] VALUES (100002,100,85.5) |
11 | INSERT INTO [Test] VALUES (100003,100,80) |
12 | INSERT INTO [Test] VALUES (100004,100,80) |
13 | INSERT INTO [Test] VALUES (100005,100,74) |
14 | INSERT INTO [Test] VALUES (101001,101,94) |
15 | INSERT INTO [Test] VALUES (101002,101,85.5) |
16 | INSERT INTO [Test] VALUES (101003,101,85.5) |
测试代码:
2 | ROW_NUMBER() OVER ( ORDER BY TestScore DESC ) as RN, |
3 | RANK() OVER ( ORDER BY TestScore DESC ) as R, |
4 | DENSE_RANK() OVER ( ORDER BY TestScore DESC ) as DR, |
5 | NTILE(3) OVER ( ORDER BY TestScore DESC ) as N3 |
执行结果:
1 | StudentID ClassID TestScore RN R DR N |
10 | 100005 100 74.0 8 8 5 3 |
通过以上的例子就很清晰了。
ROW_NUMBER
-
行号函数。用来生成数据行在结果集中的序号
语法:
ROW_NUMBER( ) OVER ([] )
可以利用ROW_NUMBER函数非常便利的实现分页功能
RANK
-
排序函数。必须配合over函数,且排序字段值相同的行号一样,同时隐藏行号会占位。
语法:
RANK() OVER ([] )
还可以利用partition进行分组排序,例如对每个班级分别按成绩排序。
DENSE_RANK
-
紧凑排序函数。与RANK函数不同的是,当排序字段值相同导致行号一样时,同时隐藏行号不占位。
语法:
DENSE_RANK ( ) OVER ([] )
NTILE
-
分区排序函数。NTILE函数需要一个参数N,这个参数支持bigint。这个函数将结果集等分成N个区,并按排序字段将已排序的记录依次轮流放入各个区内。最后每个区内会从1开始编号,NTILE函数返回这个编号。
语法:
NTILE (integer_expression) OVER ([]< order_by_clause>)