T-SQL中的APPLY用法（半翻译）_microsoft sql server apply

 

本文接上文：T-SQL 中的CROSS JOIN用法（半翻译） 同样可用于微软认证70-461: Querying Microsoft SQL Server 2012考试的学习中。

 ---------------------------------------------------------------------以下为译文-----------------------------------------------------------------------

 

原文出处：http://www.sqlservercentral.com/articles/Stairway+Series/121318/

 

从SQL Server 2005开始，微软添加了一个新的运算符用于关联一个带有函数的结果集，并把函数应用于表/视图中的每一个限定行中。这个运算符就是APPLY。从技术上来说，这个操作的底层逻辑并不是实际的“JOIN”，但由于它的用法更像是JOIN，所以通常会与关联操作相关联。APPLY操作分为两种格式：CROSS APPLY或OUTER APPLY。在本文中会分别展示这两种格式。

 

APPLY 简介：

你是否曾经写过SELECT语句去调用一个表值函数或把表值表达式的每一个记录与表匹配？如果有过这样的经历，APPLY运算符会帮到你。APPLY分为CROSS APPLY和OUTER APPLY。

第一种格式叫CROSS APPLY。它从CROSSAPPLY的一边提取表或数据集的每行的所需列值，然后作为输入值传输到另外一边的表值函数或表达式中。然后把所有关联的值使用UNION ALL运算符合并。如果表值函数针对输入数据没有满足调用条件时，函数将不返回输出，并且这部分的表或结果集将不会出现在最终结果中，因为它不能与表值函数的数据关联。

第二种格式叫OUTER APPLY。其行为和CROSSAPPLY类似，但返回不能调用表值函数/表达式的值。

为了更好理解这两种格式，下面来演示一下：

 

测试数据和函数：

 

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 ,
      ProductNameVARCHAR(100) ,
      Price MONEY
    );
INSERT  INTO dbo.Product
VALUES  ( 'Red SantaSuit', 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
    (
      @FindStringVARCHAR(100)
    )
RETURNS TABLE
AS
RETURN
    ( SELECT    ProductName,
                Price
      FROM      dbo.Product
      WHERE     ProductNameLIKE '%' + @FindString + '%'
)

 

 

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

 

使用CROSS APPLY 运算符：

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

 

USE tempdb;
GO
SELECT  *
FROM    dbo.SearchString AS S
       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的唯一区别是返回所有数据，包括没有匹配的值：

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

 

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

 

使用表值表达式：

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

USE tempdb;
GO
SELECT * FROM dbo.SearchString as S
    CROSS APPLY
    (SELECT ProductName, Price
    FROM dbo.Product
    WHERE ProductName like '%' + S.String + '%') as X

 

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

 

 

总结：

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

 

 

---------------------------------------------------------------------翻译结束-----------------------------------------------------------------------

---------------------------------------------------------------------个人总结-----------------------------------------------------------------------

针对这个题目，我个人想法不多，因为已经很多很多年没真正做开发了。但是它给我提供了一个思路，在允许的情况下，使用新版本带来的新技术并不是意见坏事。同样是70-461上的例子，其中一个使用了窗口函数的写法，比旧写法速度快了几十倍。的确震撼了我。也坚定了使用新技术的决心。

在不久的将来，我打算升级公司物流系统的数据库版本，其中可以考虑使用的技术有：

1. 列存储索引（提高静态数据的查询效率）

2. T-SQL新语法，如OFFSET（用于分页）等。

3. AlwaysOn，用于读写分离和容灾。

4. 可能使用2014的In-Memory技术。

5. 其他待定。

我对本文的感悟就是：不要固步自封，多学习新技术，特别是和你现有技术完全不同的领域，如现在你学的是关系数据库，简称OldSQL，那么不妨学学NoSQL甚至NewSQL。