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 rank1 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 rank2
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 ntile1 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 ntile2
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_rank1 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_rank2
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_number1 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_number2
2001 1 1 1001 2
1 1002 3
1
1003 4
2
2002 5
2
2003 6
上面说明了 SQL Server 中排名函数的使用方法,排名函数是 SQL Server 2005 新增的函数,这些函数大大提升了 SQL Server 数据库在统计方面的功能。