我们知道有个 SQL Server 2000 中有个 cross join 是用于交叉联接的。实际上增加 cross apply 和 outer apply 是用于交叉联接表值函数(返回表结果集的函数)的, 更重要的是这个函数的参数是另一个表中的字段。这个解释可能有些含混不请,请看下面的例子:
-- 1. cross join 联接两个表
select
*
from
TABLE_1
as
T1
cross
join
TABLE_2
as
T2
|
【此句实际上是求笛卡尔积】
-- 2. cross join 联接表和表值函数,表值函数的参数是个“常量”
select
*
from
TABLE_1 T1
cross
join
FN_TableValue(100)
|
-- 3. cross join 联接表和表值函数,表值函数的参数是“表T1中的字段”
select
*
from
TABLE_1 T1
cross
join
FN_TableValue(T1.column_a)
|
Error:
Msg 4104,
Level
16, State 1, Line 1
The multi-part identifier
"T1.column_a"
could
not
be bound.
|
最后的这个查询的语法有错误。在 cross join 时,表值函数的参数不能是表 T1 的字段, 为啥不能这样做呢?我猜可能微软当时没有加这个功能:),后来有客户抱怨后, 于是微软就增加了 cross apply 和 outer apply 来完善,请看 cross apply, outer apply 的例子:
-- 4. cross apply
select
*
from
TABLE_1 T1
cross
apply FN_TableValue(T1.column_a)
|
-- 5. outer apply
select
*
from
TABLE_1 T1
outer
apply FN_TableValue(T1.column_a)
|
cross apply 和 outer apply 对于 T1 中的每一行都和派生表(表值函数根据T1当前行数据生成的动态结果集) 做了一个交叉联接。cross apply 和outer apply 的区别在于: 如果根据 T1 的某行数据生成的派生表为空,cross apply 后的结果集 就不包含 T1 中的这行数据,而 outer apply 仍会包含这行数据,并且派生表的所有字段值都为 NULL。
使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函数作为右输入,外部表表达式作为左输入。通过对右输入求值来获得左输入每一行的计算结果,生成的行被组合起来作为最终输出。APPLY 运算符生成的列的列表是左输入中的列集,后跟右输入返回的列的列表
test4表中数据:
id name
1 LeeWhoeeUniversity
2 LeeWhoee
3 DePaul
test4score表中数据:
test4id score
1 100
1 90
1 90
1 80
2 90
2 82
2 10
APPLY
现在用APPLY操作符仅获取每个name的两个最高score记录:
分析如下:
右输入-- select top 2 * from test4score where test4id=a.id order by score desc
左输入--select * from test4
右输入求值对左输入的每一行进行计算。更进一步分析:
左输入第一行是1 LeeWhoeeUniversity
右输入计算左输入第一行id最高两个score记录得出:
id test4id score
1 1 100
3 1 90
组合行:
id name test4id score
1 LeeWhoeeUniversity 1 100
1 LeeWhoeeUniversity 1 90
以此类推,直至完成左输入所有行的计算。
结果如下:
id name test4id score
1 LeeWhoeeUniversity 1 100
1 LeeWhoeeUniversity 1 90
2 LeeWhoee 2 90
2 LeeWhoee 2 82
OUTER APPLY
outer apply 类似于LEFT JOIN,
id name test4id score
1 LeeWhoeeUniversity 1 100
1 LeeWhoeeUniversity 1 90
2 LeeWhoee 2 90
2 LeeWhoee 2 82
3 DePaul NULL NULL
由于test4score表中没有'DePaul'的记录,所以用NULL值填充。
当然还有更多的方法来实现此需求,如使用排名函数ROW_NUMBER:
结果:
name score
LeeWhoeeUniversity 100
LeeWhoeeUniversity 90
LeeWhoee 90
LeeWhoee 82
此方法是用前面介绍的ROW_NUMBER()和PARTITION BY来实现,
另外一个案例
上面的脚本中创建了一个叫做Product的表,包含了5个不同的产品。同时也创建了一个叫做SearchString的表,包含了3个不同的字符串。最后创建一个叫做FindProductLike的表值函数。该函数接收一个@FindString参数,并在Product表中找出所有包含@FindString的ProductName。
使用CROSS APPLY 运算符:
CROSS APPLY会对相关联的每一行都应用该函数
结果如下:
回看代码可以看到,代码中使用CROSSAPPLY关联SearchString表中的结果集和FindProductLike表值函数。CROSS APPLY从SearchString中获取String值,然后调用函数FindProductLike。如果函数返回数据,则与SearchString的行关联。
前两行的数据来自于字符串“Red”,当“Red”传输给函数后,返回包含该值的ProductName和Price,然后和SearchString关联,把包含“Red”值的产品返回到结果集中。第三行数据和前两行的产生原理一致,但是是由“Lights”产生。字符串“Star”由于没有在Product中得到匹配值,所以不返回结果。
使用OUTER APPLY 运算符:
该操作符和CROSS APPLY的唯一区别是返回所有数据,包括没有匹配的值:
从结果中可以看出,对于字符串“Star”,OUTER APPLY也返回结果,只是返回NULL。
使用表值表达式:
下面演示一下使用表值表达式与APPLY的操作。
从结果上来看,和CROSS APPLY无异,仅仅是把表值函数换成了表值表达式。
总结:
APPLY运算可以把数据集中的数据与表值函数或表值表达式关联,使用APPLY可以针对表值函数或表达式的数据集进行基于集合的查询。在这种情况下可以考虑使用APPLY运算符。