小议如何使用APPLY

简介

如果你打算为在结果集中的每条记录写一个调用表值函数或者表值表达式的select语句，那么你就能用到ＡＰＰＬＹ 操作符来实现。一般又两种形式写法：

第一种格式就是CROSS APPLY。这种格式可以一方面从表或者结果集中的每一行数据中取出几列数值，另一方面将取出的数据作为输入条件得到表的函数值或者表达式。每个调用表值函数或者表达式的返回行将被连接在一起，然后所有的行将通过UNION ALL 连接在一起。如果表值函数没有返回行数据由于引用了一个特殊的函数调用，那么表和结果集的行里面将不会被包含在最终的结果，因为它不能被连接在一起与任何表值函数的行。

第二种格式就是使用OUTER APPLY，这个操作符与第一种类似，但是多了额外的返回列，就是当调用的函数或者表达式没有返回任何行时，已然能够与表或者查询结果集关联在一起，只是函数和表达式返回的列为null。

为了更好地理解我们将通过几个实例来展示一下效果和作用：

测试数据和功能实例

首先我们要创建一些测试数据以及表值函数。如下：

USE test; 
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 + '%' 
    )

 

我创建了一个包含五种不同产品的表叫Product和一个叫SearchString 的表，它包含了三个不同的strings。最后我又创建了一个叫FindProductLike的函数，包含一个传入参数FindString，实现功能为： 在Product中找到所有产品名称包含传入参数值得所有数据。

使用CROSS APPLY

这个操作符将执行一个表值函数为每行关联在 在结果集中的数据，我们用下面的小例子来展示一下效果：

 

假如你回顾代码，能发现，我使用CROSS APPLY来链接了dbo.SearchString和dbo.FindProductLike 这个函数取出来的表值。操作符取到了SearchString表的String 字段然后调用表值函数FindProductLike 。所以前两行的结果集取了String 类型为Red的值，并调用函数使得ProductName 中包含Red的数据返回了ProductName 和 Price的列，然后将结果链接在一起；lights 也是一样的道理，而虽然表中含有Star，但是函数中不能匹配到有关Star的值所以不能返回相应的行。

使用OUTER APPLY

与CROSS APPLY功能相似。唯一的不同是CROSS APPLY即使没有匹配到任何行在函数中，已然能够链接表中的数据并在本来应该有函数表现的列上填充null。如图：

 

使用表值表达式

到目前为止我们仅仅展示了APPLY 在一个结果集和一个表值函数之间的例子。当然它也是能与一个表值表达式一起应用的。来看看具体如何实现，如图：

正如实例中表现的一样，我们也能通过使用表值表达式的形式来实现之前调用表值函数实现的结果。同样的，我们也能对表值表达式使用

OUTER APPLY 来实现外链接。这个例子我就不再列举了，有兴趣的可以自己尝试一下。

总结：

APPLY操作符，让我们能够将结果集中的行与表值函数或者表值表达式中的结果连接在一起。通过使用操作符我们能很快实现一下之前复杂的表链接或者是相关子查询等等T-SQL语句，使得代码简介高效。