MSSQL ROW_NUMBER()、RANK()和DENSE_RANK()的用法

 1、ROW_NUMBER()

说明：返回结果集分区内行的序列号，每个分区的第一行从 1 开始。
语法：ROW_NUMBER () OVER ( [ <partition_by_clause> ] <order_by_clause> ) 。
备注：ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。
参数：<partition_by_clause> ：将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。
<order_by_clause>：确定将 ROW_NUMBER 值分配给分区中的行的顺序。
返回类型：bigint 。

2、RANK()

说明：返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。
语法：RANK () OVER ( [ < partition_by_clause > ] < order_by_clause > )
备注：如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名。
例如，如果两位顶尖销售员具有同样的 SalesYTD 值，他们将并列第一。
由于已有两行排名在前，所以具有下一个最大 SalesYTD 的销售人员将排名第三。
因此，RANK 函数并不总返回连续整数。
用于整个查询的排序顺序决定了行在结果集中的显示顺序。这也隐含了行在每个分区中的排名。
参数：< partition_by_clause > ：将 FROM 子句生成的结果集划分为要应用 RANK 函数的分区。
< order_by_clause >：确定将 RANK 值应用于分区中的行时所基于的顺序。
返回类型：bigint

3、DENSE_RANK()

说明：返回结果集分区中行的排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。
语法：DENSE_RANK () OVER ( [ < partition_by_clause > ] < order_by_clause > )
备注：如果有两个或多个行受同一个分区中排名的约束，则每个约束行将接收相同的排名。
例如，如果两位顶尖销售员具有相同的 SalesYTD 值，则他们将并列第一。
接下来 SalesYTD 最高的销售人员排名第二。该排名等于该行之前的所有行数加一。
因此，DENSE_RANK 函数返回的数字没有间断，并且始终具有连续的排名。
整个查询所用的排序顺序确定了各行在结果中的显示顺序。这说明排名第一的行可以不是分区中的第一行。
参数：< partition_by_clause > ：将 FROM 子句所生成的结果集划分为数个将应用 DENSE_RANK 函数的分区。
< order_by_clause >：确定将 DENSE_RANK 值应用于分区中各行的顺序。
返回类型：bigint

示例：

--rank()
select * from
(
select rank() over(partition by sc_no order by s_avgrade desc) as row,
* from MyDataBase.dbo.student_view
) v
where row=1
go

--row_number()
select * from
(
select row_number() over(partition by sc_no order by s_avgrade desc) as row,
* from MyDataBase.dbo.student_view
) v
where row=1
go

--DENSE_RANK()
select * from
(
select DENSE_RANK() over(partition by sc_no order by s_avgrade desc) as row,
* from MyDataBase.dbo.student_view
) v
where row=1 

/*

row                  sc_no       sc_name                                            s_no     s_name   s_sex s_birthday              s_speciality                                       s_avgrade                               s_dept
-------------------- ----------- -------------------------------------------------- -------- -------- ----- ----------------------- -------------------------------------------------- --------------------------------------- --------------------------------------------------
1                    1           清华大学                                               12010012 张红       女     1989-12-09 00:00:00     网络工程                                               94.5                                    信息工程系
1                    1           清华大学                                               20110017 张伟       男     1991-04-09 00:00:00     软件工程                                               94.5                                    信息工程系
1                    2           浙江大学                                               12010004 王雅       女     1989-03-09 00:00:00     计算机应用技术                                            94.0                                    计算机系
1                    3           武汉大学                                               12010005 张杰       男     1989-12-19 00:00:00     计算机应用技术                                            97.5                                    计算机系
1                    4           贵州大学                                               20121042 赵倩       女     1990-06-16 00:00:00     软件工程                                               90.5                                    信息工程系
1                    4           贵州大学                                               20121043 刘星       男     1980-06-16 00:00:00     网络工程                                               90.5                                    信息工程系
(6 行受影响)
row                  sc_no       sc_name                                            s_no     s_name   s_sex s_birthday              s_speciality                                       s_avgrade                               s_dept
-------------------- ----------- -------------------------------------------------- -------- -------- ----- ----------------------- -------------------------------------------------- --------------------------------------- --------------------------------------------------
1                    1           清华大学                                               12010012 张红       女     1989-12-09 00:00:00     网络工程                                               94.5                                    信息工程系
1                    2           浙江大学                                               12010004 王雅       女     1989-03-09 00:00:00     计算机应用技术                                            94.0                                    计算机系
1                    3           武汉大学                                               12010005 张杰       男     1989-12-19 00:00:00     计算机应用技术                                            97.5                                    计算机系
1                    4           贵州大学                                               20121042 赵倩       女     1990-06-16 00:00:00     软件工程                                               90.5                                    信息工程系
(4 行受影响)
row                  sc_no       sc_name                                            s_no     s_name   s_sex s_birthday              s_speciality                                       s_avgrade                               s_dept
-------------------- ----------- -------------------------------------------------- -------- -------- ----- ----------------------- -------------------------------------------------- --------------------------------------- --------------------------------------------------
1                    1           清华大学                                               12010012 张红       女     1989-12-09 00:00:00     网络工程                                               94.5                                    信息工程系
1                    1           清华大学                                               20110017 张伟       男     1991-04-09 00:00:00     软件工程                                               94.5                                    信息工程系
1                    2           浙江大学                                               12010004 王雅       女     1989-03-09 00:00:00     计算机应用技术                                            94.0                                    计算机系
1                    3           武汉大学                                               12010005 张杰       男     1989-12-19 00:00:00     计算机应用技术                                            97.5                                    计算机系
1                    4           贵州大学                                               20121042 赵倩       女     1990-06-16 00:00:00     软件工程                                               90.5                                    信息工程系
1                    4           贵州大学                                               20121043 刘星       男     1980-06-16 00:00:00     网络工程                                               90.5                                    信息工程系
(6 行受影响)

*/