sql server cross/outer apply 用法

时间:2024-03-11 15:34:29

这是 sql server 帮助文档关于apply的描述:

      使用 APPLY 运算符(2005或以上版本)可以为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函数作为右输入,外部表表达式作为左输入。通过对右输入求值来获得左输入每一行的计算结果,生成的行被组合起来作为最终输出。APPLY 运算符生成的列的列表是左输入中的列集,后跟右输入返回的列的列表。

      APPLY 有两种形式: CROSS APPLY 和 OUTER APPLY。CROSS APPLY 仅返回外部表中通过表值函数生成结果集的行。OUTER APPLY 既返回生成结果集的行,也返回不生成结果集的行,其中表值函数生成的列中的值为 NULL。

看一下例子:

select * from table1 join MyFunction(1) on 1=1

MyFunction 的参数是一个常量,可以返回一个表。

但有时候我们希望以 table1 的字段作为参数,传进函数去计算,像:

select * from table1 join MyFunction(id) on 1=1

这样是会出错的。这个时候我们就可以用 apply 来实现了。例如:

select * from table1 cross apply MyFunction(id) on 1=1

简单的说,apply 允许我们将前面结果集每一行的数据作为参数,传递到后面的表达式,后面的表达式可以是一个表值函数,或者select结果集。

实际项目应用:

产品表和点击率统计表,按产品最近一个月点击率排序。

如果不用apply,实现起来就比较麻烦,用 apply 实现起来大概就是这样的:

;with cteResult as(
   select row_number() over(order by HitCount) as rowid, 
          ProductID,
          ProductName 
     from ProductInfo pi with(nolock) 
    outer apply(select sum(HitCount) HitCount from HitStatisticsInfo hsi 
    where hsi.TargetID = pi.ProductID
      and hsi.HitTime >= \'2015-08-08\' 
    group by TargetID) hsi 
    where pi.IsDel = 0) 
select * from cteResult where rowid between 1 and 20

至于 cross apply 与 outer apply 的区别就好像left join 与 join 的区别。如果 apply 不生成行,outer apply 也会返回该行,而 cross apply 则不会输出该行。

so,当你在需要将某个字段的值作为参数使用时,或者用join实现起来比较复杂时,就可以考虑apply来实现。