SQL Server 中排名函数（Ranking Functions）的使用方法

SQL Server 提供了一组排名函数（Ranking Functions），为结果集分区中的每一行返回一个排名值。根据所用到的函数和选项，某些行的排名值可能相同。排名函数包括RANK, NTILE, DENSE_RANK, ROW_NUMBER 四种，这四种函数使用方法很相似，只是功能稍微有所不同，我们用一些例子来说明用法。

group_id （组编号）	user_id（学号）	score（成绩）
1	1001	83
1	1002	83
1	1003	78
2	2001	90
2	2002	78
2	2003	73

上面是一张成绩表，表名为 table_ranking，包含 class_id, student_id, score 3个字段，数据都列在表中。使用不同的函数，我们可以取得不同的排名值，我们用排名函数分别做查询，可以得到不同的结果。

1. RANK 函数。RANK 函数返回结果集分区内每行的排名，从1开始，排名值为前一行的排名值加一。如果存在多个行与一个排名关联，则这些关联行将得到相同的排名值，后续行的排名值会与前面关联行的排名值隔开，发生不连续的情况。
语法

RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

参数
partition_by_clause. 分区字段，为 PARTITION BY column_name... 这样的格式。不同分区排名值的计算是互相独立的。
order_by_clause. 排序字段，为 ORDER BY column_name... 这样的格式。在同一分区内，依据此字段排序，计算排名值。
对1，2组分别按成绩由高往低排名的 sql 查询语句如下：

SELECT group_id, user_id, RANK () OVER ( PARTITION BY group_id ORDER BY score DESC ) AS rank FROM table_ranking

查询结果

group_id	user_id	rank
1	1001	1
1	1002	1
1	1003	3
2	2001	1
2	2002	2
2	2003	3

对于1，2组一起按成绩由高往低排名的 sql 查询语句如下：

SELECT group_id, user_id, RANK () OVER ( PARTITION BY group_id ORDER BY score DESC ) AS rank FROM table_ranking

查询结果

group_id	user_id	rank
2	2001	1
1	1001	2
1	1002	2
1	1003	4
2	2002	4
2	2003	6

2. NTILE 函数。NTILE 函数将有序分区中的行分配到指定数目的组中。每个组有编号，从1开始，对于每一行，NTILE 返回对应的组号。组号越小的组取得的记录行越靠近查询结果前列，组号越大的组取得的记录行越靠近查询结果后列。需要说明的是，如果分区的行数不能被组数整除，那么排在序号较小的组将获得更多的行，同时每组包含的行数量将会尽量保持相同，任意两组间包含的行数量差别不会大于一。
语法

NTILE ( integer_expression ) OVER ( [ partition_by_clause ] order_by_clause )

参数
integer_expression. 正整数，为 int 或 bigint 类型，表示每个分区分成组的数量。
partition_by_clause. 分区字段，为 PARTITION BY column_name... 这样的格式。不同分区排名值的计算是互相独立的。
order_by_clause. 排序字段，为 ORDER BY column_name... 这样的格式。在同一分区内，依据此字段排序，计算排名值。
对1，2组分别按成绩由高往低分成两组的 sql 查询语句如下：

SELECT group_id, user_id, NTILE ( 2 ) OVER ( PARTITION BY group_id ORDER BY score DESC ) AS ntile FROM table_ranking

查询结果

group_id	user_id	ntile
1	1001	1
1	1002	1
1	1003	2
2	2001	1
2	2002	1
2	2003	2

对于1，2组一起按成绩由高往低分成两组的 sql 查询语句如下：

SELECT group_id, user_id, NTILE ( 2 ) OVER ( ORDER BY score DESC ) AS ntile FROM table_ranking

查询结果

group_id	user_id	ntile
2	2001	1
1	1001	1
1	1002	1
1	1003	2
2	2002	2
2	2003	2

3. DENSE_RANK 函数。DENSE_RANK 函数和 RANK 函数功能一样，返回结果集分区内每行的排名，唯一的区别是 DENSE_RANK 在具有相同排名值的情况下，排名值也保持连续，不会间断。
语法

DENSE_RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

参数
partition_by_clause. 分区字段，为 PARTITION BY column_name... 这样的格式。不同分区排名值的计算是互相独立的。
order_by_clause. 排序字段，为 ORDER BY column_name... 这样的格式。在同一分区内，依据此字段排序，计算排名值。
对1，2组分别按成绩由高往低计算排名的 sql 查询语句如下：

SELECT group_id, user_id, DENSE_RANK ( ) OVER ( PARTITION BY group_id ORDER BY score DESC ) AS dense_rank FROM table_ranking

查询结果

group_id	user_id	dense_rank
1	1001	1
1	1002	1
1	1003	2
2	2001	1
2	2002	2
2	2003	3

对于1，2组一起按成绩由高往低计算排名的 sql 查询语句如下：

SELECT group_id, user_id, DENSE_RANK ( ) OVER ( ORDER BY score DESC ) AS dense_rank FROM table_ranking

查询结果

group_id	user_id	dense_rank
2	2001	1
1	1001	2
1	1002	2
1	1003	3
2	2002	3
2	2003	4

4. ROW_NUMBER 函数。ROW_NUMBER 函数返回结果集内每行的行号，行号从1开始，在结果集分区内唯一，并保持连续。
语法

ROW_NUMBER ( ) OVER ( [ partition_by_clause ] order_by_clause )

参数
partition_by_clause. 分区字段，为 PARTITION BY column_name... 这样的格式。不同分区排名值的计算是互相独立的。
order_by_clause. 排序字段，为 ORDER BY column_name... 这样的格式。在同一分区内，依据此字段排序，计算排名值。
对1，2组分别按成绩由高往低计算行号的 sql 查询语句如下：

SELECT group_id, user_id, ROW_NUMBER ( ) OVER ( PARTITION BY group_id ORDER BY score DESC ) AS row_number FROM table_ranking

查询结果

group_id	user_id	row_number
1	1001	1
1	1002	2
1	1003	3
2	2001	1
2	2002	2
2	2003	3

对于1，2组一起按成绩由高往低计算行号的 sql 查询语句如下：

SELECT group_id, user_id, ROW_NUMBER ( ) OVER ( ORDER BY score DESC ) AS row_number FROM table_ranking

查询结果

group_id	user_id	row_number
2	2001	1
1	1001	2
1	1002	3
1	1003	4
2	2002	5
2	2003	6

上面说明了 SQL Server 中排名函数的使用方法，排名函数是 SQL Server 2005 新增的函数，这些函数大大提升了 SQL Server 数据库在统计方面的功能。