高级T-SQL进阶系列 （二）【上篇】：使用 APPLY操作符

【译注：此文为翻译，由于本人水平所限，疏漏在所难免，欢迎探讨指正】

  原文链接：传送门。

伴随着SQL SERVER 2005的发布，微软增加了一个新的操作符，它允许你将一个记录集与一个函数进行关联，然后对表（或者视图）的每一个指定列应用这个函数。这个新的操作符便是APPLY操作符。技术上来说，这个操作符实际上并不是一个JOIN，但是因为它用起来像是一个JOIN，它通常被成为关联操作。APPLY操作符具有两种形式，CROSS APPLY以及OUTER APPLY，在这篇文章中，我将向你解释这两种格式的差异，并以例子来演示每种格式是如何工作的。

 

介绍APPLY操作符

你曾经是否想要写过一个SELECT语句来对数据集中的每一行调用表值函数或计算表值表达式？如果是的话那么你将乐于了解APPLY操作符允许你进行这样的处理。APPLY操作符具有两种形式。

第一种形式是CROSS APPLY，其使用CROSS APPLY操作符一边的集合中每一行的列值并将它们作为CROSS APPLY操作符另一边的表值函数或表达式的输入项。每次对表值函数或者表达式调用返回的行会与触发表值函数调用的值所在的行进行关联，所有关联的行的集合最后再用UNION JOIN操作符关联起来。如果对于某一个特定的调用，表值函数并没有返回任何数据行，那么表或者记录行则不会被包含在最早的结果集中，因为它们不能与任何表值函数行进行关联。

APPLY操作符能够使用的第二种格式是OUTER APPLY。当表值函数或者表达式返回数据行时，这个操作符运行得如同CROSS APPLY。但是其有一个额外的特性。当表值函数被不会返回任何行的数据行所调用时，OUTER APPLY操作符同样会返回表或者记录集。

为了更好的理解这两种形式的APPLY操作符是如何工作的。让我们来查看下每个形式的操作符的一些示例。

 

示例的测试数据与函数

在我能够向你演示APPLY操作符的不同示例之前，我需要创建一些测试数据和一个表值函数。我的测试数据表和函数可以在列表1中被找到：

USE tempdb;
GO
IF object_id('dbo.Product') IS NOT NULL 
    DROP TABLE dbo.Product;
IF object_id('dbo.SearchString') IS NOT NULL 
    DROP TABLE dbo.SearchString;
IF object_id('dbo.FindProductLike') IS NOT NULL
    DROP FUNCTION dbo.FindProductLike;
CREATE TABLE dbo.Product
    (
      ID INT IDENTITY ,
      ProductName VARCHAR(100) ,
      Price MONEY
    );
INSERT  INTO dbo.Product
VALUES  ( 'Red Santa Suit', 199.99 ),
        ( 'Candy Canes', 1.99 ),
        ( 'Fake Snow', 2.99 ),
        ( 'Red Bells', 49.99 ),
        ( 'LED Lights', 6.99 );
CREATE TABLE dbo.SearchString
    (
      ID INT IDENTITY ,
      String VARCHAR(100)
    );
INSERT  INTO dbo.SearchString
VALUES  ( 'Red' ),
        ( 'Lights' ),
        ( 'Star' );
GO
CREATE FUNCTION dbo.FindProductLike
    (
      @FindString VARCHAR(100)
    )
RETURNS TABLE
AS
RETURN
    ( SELECT    ProductName ,
                Price
      FROM      dbo.Product
      WHERE     ProductName LIKE '%' + @FindString + '%'
    )

 列表1：创建表和表值函数

列表1的代码创建了名为Product的表，其包含了5个不同的产品。同样我创建了一个名为SearchString的表，其包含了三个字符串。最后我创建了名为dbo.FindProductLike的表值函数。表值函数dbo.FindProductLike接受一个参数：@FindString。这个表值函数使用了提供的参数，并返回ProductName包含了传递进@FindString参数的字符串的那些数据行。

使用CROSS APPY操作符

CROSS APPLY操作符会对其所涉及的记录集中的每一行执行一个表值函数。为了演示这是如何工作的，让我来运行列表2的代码：

USE tempdb;
GO
SELECT  *
FROM    dbo.SearchString AS S
        CROSS APPLY dbo.FindProductLike(S.String);

列表2：CROSS APPLY操作符的例子

当我运行列表2的代码，随着查询结果以文本形式来显示，我得到了如报告1的输出：

ID    String           ProductName                Price
----- ---------------- -------------------------- ---------
1     Red              Red Santa Suit             199.99
1     Red              Red Bells                  49.99
2     Lights           LED Lights                 6.99

报告1：通过运行代码2产生的输出

 

如果你查看列表2的代码你会看到我使用CROSS APPLY操作符来将表dbo.SearchString的数据行与表值函数dbo.FindProductLike的结果进行关联。CROSS APPLY操作符使用来自于dbo.SearchString表的字符串值并且调用了表值函数dbo.FindProductLike。如果表值函数返回了任何数据行， 它们将会与dbo.SearchString的行进行关联。

结果表的前两行是由于使用了字符串值“Red”，并使用它调用表值函数dbo.FindProductLike而产生的。当“Red”被传递给表值函数时，对于那些ProductName 列包含字符“Red”的那些行，ProductName和Price列便被返回。由表值函数返回的这两行随后与包含“Red”的dbo.SearchString数据行进行关联，于是便产生了报告1所示的前两行数据。

报告的第三行的结果和前两行产生的方式是一样的，除过这次函数是用“Lights”值来调用的，其仅仅匹配了一个单独的ProductName值。因此仅仅产生了一个单独的数据行。dbo.SearchString表的最后一行，其查询列值为“Star”，其在Product表不会匹配到任何ProductName的值。所以对于包含“Star”值的dbo.SearchString数据行，在报告1中不会产生任何数据行。

----To be continued.