Cross apply 和outer apply

      我们知道有个 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记录：

[sql]  view plain  copy

 

1. select * from test4 a  
2. cross apply  
3. (  
4.     select top 2 * from test4score where test4id=a.id order by score desc  
5. ) b  

分析如下：

右输入-- 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,

[sql]  view plain  copy

 

1. select * from test4 a  
2. outer apply  
3. (  
4.     select top 2 * from test4score where test4id=a.id order by score desc  
5. ) b  

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：

[sql]  view plain  copy

 

1. select b.name,a.score from(  
2. select *,ROW_NUMBER()over(partition by test4id order by score desc) as rum from test4score  
3. ) a   
4. inner join test4 b on b.id=a.test4id where rum < 3  

结果：

name                        score
LeeWhoeeUniversity    100
LeeWhoeeUniversity    90
LeeWhoee            90
LeeWhoee            82

此方法是用前面介绍的ROW_NUMBER()和PARTITION BY来实现， 

另外一个案例

[sql]  view plain  copy

 print ?

1. USE tempdb;  
2. GO  
3. IF object_id('dbo.Product') IS NOT NULL  
4.     DROP TABLE dbo.Product;  
5. IF object_id('dbo.SearchString') IS NOT NULL  
6.     DROP TABLE dbo.SearchString;  
7. IF object_id('dbo.FindProductLike') IS NOT NULL  
8.     DROP FUNCTION dbo.FindProductLike;  
9.    
10. CREATE TABLE dbo.Product  
11.     (  
12.       ID INT IDENTITY ,  
13.       ProductNameVARCHAR(100) ,  
14.       Price MONEY  
15.     );  
16. INSERT  INTO dbo.Product  
17. VALUES  ( 'Red SantaSuit', 199.99 ),  
18.         ( 'Candy Canes', 1.99 ),  
19.         ( 'Fake Snow', 2.99 ),  
20.         ( 'Red Bells', 49.99 ),  
21.         ( 'LED Lights', 6.99 );  
22.    
23. CREATE TABLE dbo.SearchString  
24.     (  
25.       ID INT IDENTITY ,  
26.       String VARCHAR(100)  
27.     );  
28.    
29. INSERT  INTO dbo.SearchString  
30. VALUES  ( 'Red' ),  
31.         ( 'Lights' ),  
32.         ( 'Star' );  
33. GO  
34.    
35. CREATE FUNCTION dbo.FindProductLike  
36.     (  
37.       @FindStringVARCHAR(100)  
38.     )  
39. RETURNS TABLE  
40. AS  
41. RETURN  
42.     ( SELECT    ProductName,  
43.                 Price  
44.       FROM      dbo.Product  
45.       WHERE     ProductNameLIKE '%' + @FindString + '%'  
46. )  

创建表和表值函数

 

 

上面的脚本中创建了一个叫做Product的表，包含了5个不同的产品。同时也创建了一个叫做SearchString的表，包含了3个不同的字符串。最后创建一个叫做FindProductLike的表值函数。该函数接收一个@FindString参数，并在Product表中找出所有包含@FindString的ProductName。

 

使用CROSS APPLY 运算符：

CROSS APPLY会对相关联的每一行都应用该函数

 

[sql]  view plain  copy

 print ?

1. USE tempdb;  
2. GO  
3. SELECT  *  
4. FROM    dbo.SearchString AS S  
5.        CROSS APPLYdbo.FindProductLike(S.String);  

结果如下：

 

 

回看代码可以看到，代码中使用CROSSAPPLY关联SearchString表中的结果集和FindProductLike表值函数。CROSS APPLY从SearchString中获取String值，然后调用函数FindProductLike。如果函数返回数据，则与SearchString的行关联。

前两行的数据来自于字符串“Red”，当“Red”传输给函数后，返回包含该值的ProductName和Price，然后和SearchString关联，把包含“Red”值的产品返回到结果集中。第三行数据和前两行的产生原理一致，但是是由“Lights”产生。字符串“Star”由于没有在Product中得到匹配值，所以不返回结果。

 

 

使用OUTER APPLY 运算符：

该操作符和CROSS APPLY的唯一区别是返回所有数据，包括没有匹配的值：

[sql]  view plain  copy

 print ?

1. USE tempdb;  
2. GO  
3. SELECT  *  
4. FROM    dbo.SearchString AS S  
5.        OUTER APPLYdbo.FindProductLike(S.String);  

 

从结果中可以看出，对于字符串“Star”，OUTER APPLY也返回结果，只是返回NULL。

 

使用表值表达式：

下面演示一下使用表值表达式与APPLY的操作。

[sql]  view plain  copy

 print ?

1. USE tempdb;  
2. GO  
3. SELECT * FROM dbo.SearchString as S  
4.     CROSS APPLY  
5.     (SELECT ProductName, Price  
6.     FROM dbo.Product  
7.     WHERE ProductName like '%' + S.String + '%') as X  

 

从结果上来看，和CROSS APPLY无异，仅仅是把表值函数换成了表值表达式。

 

 

总结：

APPLY运算可以把数据集中的数据与表值函数或表值表达式关联，使用APPLY可以针对表值函数或表达式的数据集进行基于集合的查询。在这种情况下可以考虑使用APPLY运算符。