SQL Server提供了4个排名函数:ROW_NUMBER(), RANK(),DENSE_RANK()和NTILE()。下面通过示例重点谈谈这四个函数的使用。
1、ROW_NUMBER()
返回结果集分区内行的序列号,每个分区的第一行从 1 开始。ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。
下面的查询按照数学成绩逆序排列:
SELECT ID,
ROW_NUMBER() OVER (ORDER BY score DESC) AS 'Number',
studentid,
classid,
courseid,
score
FROM StudentScore
WHERE courseid = 8
SELECT ID,
ROW_NUMBER() OVER (ORDER BY score DESC) AS 'Number',
studentid,
classid,
courseid,
score
FROM StudentScore
WHERE courseid = 8
2,RANK()和DENSE_RANK()
(1)、RANK()函数
返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。如果两个或多个行与一个排名关联,则每个关联行将得到相同的排名
SELECT ID,
RANK() OVER (ORDER BY score DESC) AS 'Number',
studentid,
classid,
courseid,
score
FROM StudentScore
WHERE courseid = 8
RANK() OVER (ORDER BY score DESC) AS 'Number',
studentid,
classid,
courseid,
score
FROM StudentScore
WHERE courseid = 8
注意,它和ROW_NUMBER()的异同点,您应该已经知道了:
a、RANK函数和ROW_NUMBER函数类似,它们都是用来对结果进行排序。
b、不同的是,ROW_NUMBER函数为每一个值生成唯一的序号,而RANK函数为相同的值生成相同的序号。
b、不同的是,ROW_NUMBER函数为每一个值生成唯一的序号,而RANK函数为相同的值生成相同的序号。
(2)、DENSE_RANK()函数
返回结果集分区中行的排名,在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。如果有两个或多个行受同一个分区中排名的约束,则每个约束行将接收相同的排名
SELECT ID,
DENSE_RANK() OVER (ORDER BY score DESC) AS 'Number',
studentid,
classid,
courseid,
score
FROM StudentScore
WHERE courseid = 8
DENSE_RANK() OVER (ORDER BY score DESC) AS 'Number',
studentid,
classid,
courseid,
score
FROM StudentScore
WHERE courseid = 8
两个86分的学生对应的序号都是3,而接着排在它们下面的序号是4(也就是说DENSE_RANK()函数查询的序号是类似ROW_NUMBER()那样连续的,但是对于相同值的行生成相同的序号,从这一点上来说,对于相同查询条件和排序的查询,ROW_NUMBER()函数查询的结果集是DENSE_RANK()函数查询的结果的子集)。这也是我们可以总结出的RANK和DENSE_RANK()这两个函数的最大的不同点。
3、NTILE()
NTILE函数把结果中的行关联到组,并为每一行分配一个所属的组的编号,编号从一开始。对于每一个行,NTILE 将返回此行所属的组的编号。
如果分区的行数不能被 integer_expression 整除,则将导致一个成员有两种大小不同的组。按照 OVER 子句指定的顺序,较大的组排在较小的组前面
如果分区的行数不能被 integer_expression 整除,则将导致一个成员有两种大小不同的组。按照 OVER 子句指定的顺序,较大的组排在较小的组前面
SELECT ID,
NTILE(6) OVER (ORDER BY classid DESC) AS 'GROUP NUMBER',
studentid,
classid,
courseid,
score
FROM StudentScore
WHERE courseid = 8
NTILE(6) OVER (ORDER BY classid DESC) AS 'GROUP NUMBER',
studentid,
classid,
courseid,
score
FROM StudentScore
WHERE courseid = 8