SQL中ROW_NUMBER和APPLY在处理TOP N等类似问题的一点比较
ROW_NUMBER 函数可以向查询的结果行提供连续的整数值,通常配合OVER来使用:
() OVER(
[PARTITION BY ]
ORDER BY )
PARTITION BY是一个很实用的应用,可以在行组内部独立地分段计算排序值,而不是为作为一个组的所有表行计算排序值。
APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函数作为右输入,外部表表达式作为左输入。通过对右输入求值来获得左输入每一行的计算结果,生成的行被组合起来作为最终输出。APPLY 运算符生成的列的列表是左输入中的列集,后跟右输入返回的列的列表。要使用APPLY,数据库兼容级别至少为90(SQL Server 2005及其更高的版本)。
APPLY 有两种形式:CROSS APPLY 和 OUTER APPLY。CROSS APPLY 仅返回外部表中通过表值函数生成结果集的行。OUTER APPLY 既返回生成结果集的行,也返回不生成结果集的行,其中表值函数生成的列中的值为 NULL。
下面简介一个简单的应用(以NOTHWIND数据库为例):
1. 查询出每个雇员最近销售的两笔订单(table:Employees, Orders)
使用ROW_NUMBER来实现:按照EmployeeID做分段partition排序,把最近的两个订单排在最前面,编号分别为1和2。外层查询直接取出这编号小于等于2的就是最新的两个订单,CODE如下。
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
select A.* from (
select ROW_NUMBER()over(partition by O.employeeID order by O.orderdate desc) as ROW,E.LastName,E.FirstName,O.*
from Employees E join Orders O on E.EmployeeID=O.EmployeeID
)A where A.ROW<=2
Order by A.EmployeeID
使用CROSS APPLY来实现:因为APPLY是为买个查询操作的外部行返回值,所以每个行返回最新的两个订单即可,CODE如下。
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
select E.FirstName,E.LastName,OT.* from Employees E
CROSS APPLY (Select Top (2) * from Orders O where O.EmployeeID=E.EmployeeID order by O.OrderDate DESC) as OT
Order by E.EmployeeID
--注:APPLY中的查询可以写成一个函数,参数为EmployeeID
2. 比较消耗的IO
用SET STATISTICS IO ON测试两段查询的IO使用情况:
使用ROW_NUMBER会产生一个临时的工作表,对表Order的逻辑查询22次;
使用CROSS APPLY逻辑查询627次,因为对每一个EmployeeID,都会独自执行一次APPLY中的查询,找出两个Order。
逻辑读的次数:IO(ROW_NUMBER)<<IO(APPLY)
3. 比较消耗的TIME
对这两个查询来说,看起来APPLY消耗的时间更少。
消耗的时间:TIME(ROW_NUMBER)>TIME(APPLY)
4. 比较执行计划
有不同的执行计划:
ROW_NUMBER其中Clustered Index Scan,Hash Hatch, Sort花费最多。
APPLY其中Key Lookup花费最多。
从以上的比较可以看出,ROW_NUMBER会有较少的Logical Read/IO,但会花费较多的时间。两种方法都能够很好的处理TOP N之类的需求,都会有很好的处理效果,只是根据系统的loading来选择合适的处理方法。
我个人比较喜欢用ROW_NUMBER。
另:由于测试环境的硬件配置及数据量的差异,测试结果会有所不同。