SQL Server中CROSS APPLY和OUTER APPLY应用

àì夳堔傛蜴生んèń 2022-03-31 03:14 116阅读 0赞

1.什么是Cross Apply和Outer Apply ？  
我们知道SQL Server 2000中有Cross Join用于交叉联接的。实际上增加Cross Apply和Outer Apply是用于交叉联接表值函数（返回表结果集的函数）的，更重要的是这个函数的参数是另一个表中的字段。SQL语句示例测试：

--cross join联接两个表
    select * from TABLE_1 as T1
    cross join TABLE_2 as T2
    
    --cross join联接表和表值函数，表值函数的参数是个“常量”
    select * from TABLE_1 T1
    cross join FN_TableValue(100)
    
    --cross join联接表和表值函数，表值函数的参数是“表T1中的字段”
    select * from TABLE_1 T1
    cross join FN_TableValue(T1.column_a)
    
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "T1.column_a" could not be bound.

最后的这个查询的语法有错误。在Cross Join时，表值函数的参数不能是表T1的字段，为啥不能这样做？微软在2005版本就增加了Cross Apply和Outer Apply来完善，请看Cross Apply和Outer Apply的例子：

--cross apply实现
    select * from TABLE_1 T1
    cross apply FN_TableValue(T1.column_a)
    
    --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。

2.Cross Apply和Outer Apply的理解  
新增的APPLY表运算符把右表表达式应用到左表表达式中的每一行。它不像JOIN那样先计算哪个表表达式都可以，APPLY必选先逻辑地计算左表达式。这种计算输入的逻辑顺序允许把右表达式关联到左表表达式。  
APPLY有两种形式，一个是OUTER APPLY，一个是CROSS APPLY，区别在于指定OUTER，意味着结果集中将包含使右表表达式为空的左表表达式中的行，而指定CROSS，则相反，结果集中不包含使右表表达式为空的左表表达式中的行。

可以理解为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函数作为右输入，外部表表达式作为左输入。通过对右输入求值来获得左输入每一行计算结果，生成的行被组合起来作为最终输出。APPLY运算符生成列的列表是左输入中的列集，后跟右输入返回列的列表。  
APPLY的两种形式，CROSS APPLY仅返回外部表中通过表值函数生成结果集的行。OUTER APPLY既返回生成结果集的行，也返回不生成结果集的行，其中表值函数生成的列中的值为 NULL。

语法：  
APPLY 操作符作用于 FROM 字句中，它的具体语法如下：  
FROM left\_table\_source   
 　　\{ CROSS | OUTER \} APPLY right\_table\_source  
在上面的语法中，left\_table\_source 表示坐输入；right\_table\_source 表示右输入，一般是表值函数，但亦可是视图、表、派生表。

3.示例测试  
(1).CROSS APPLY   
说明：只返回外部表中包含的数据行，这些航从表值函数中生成结果集。  
比如：LargeTable表中的某一列存储的数据是以“:”号分隔的数据，我们处理的时候，可能要先把这个值，先分隔，然后把分隔后的每个值单独一行放在一张表中，然后对这个表做处理。原始数据（LargeTable表）：  
![14222923-5abe7db7dc6e4fd19a854d5e36a30c8c.png][]  
为了简单，先用其中id=2的一行处理，这些以:号分隔的数据，可能是我们某张表的主键（t1），我们可能需要把这些数值提出来，放在一张临时表中，和t1表关联，做一些处理。处理分隔数据效果图：  
![14223038-eb6d90ef0d6d4a74b931bcaace803a64.png][]  
如果用之前的版本处理这个操作的话，应该复杂些。这只是用其中一行做的处理，如果我们用上图的3行都做这样处理，把三行以:号分隔的数值都放在一个表中，该怎么处理呢？  
用APPLY表运算符一行语句就能简单的处理以上操作:

SELECT a FROM dbo.LargeTable AS LT    --实际表  
    CROSS APPLY dbo.split(LT.Name,':')    --自定义表值函数，处理以某个字符分隔的数据，把这些数据，返回一张表  
    WHERE a <> ''                 --去掉结果表中a字段为空的数据

执行结果：  
![14231638-a009d2c05ae544bf8922ccfa147704b2.png][]  
需要额外定义的就是那个自定义表值函数（Split），类似.Net中Split操作，代码如下：

CREATE Function [dbo].[Split]
    (
        @Sql varchar(8000),
        @Splits varchar(10)
    )  
    returns @temp Table (a varchar(100))
    As
    Begin
        Declare @i Int
        Set @Sql = RTrim(LTrim(@Sql))
        Set @i = CharIndex(@Splits,@Sql)
         
        While @i >= 1
        Begin
            Insert @temp Values(Left(@Sql,@i-1)) 
            Set @Sql = SubString(@Sql,@i+1,Len(@Sql)-@i) 
            Set @i = CharIndex(@Splits,@Sql)
        End
         
        If @Sql <> ''
            Insert @temp Values (@Sql)
        Return
    End
     
    ------------调用示例-------------
    SELECT * FROM dbo.Split('581::579::519::279::406::361::560',':')

(2).OUTER APPLY  
说明：无论联接是否存在，都会从外部表和表值函数中返回数据行。这与OUTER JOIN 很类似。如果表值函数中没有行存在，则从函数中返回的列值为NULL。  
场景描述：有个供货商表（Supplier）和供货商产品表（Products）,我们要取每一个供货商中单价最高的两个产品。其表数据：  
![14223823-388065c030334cc4823e373ecebce094.png][] ![14223839-d6177fbb93e2485a97a796abd04b0af9.png][]  
我们创建一个自定义表值函数（dbo.fn\_top\_products），该函数根据供货商ID返回单价最高的两个商品。下面用OUTER APPLY形式来查询，看会出现什么结果：

IF OBJECT_ID('dbo.fn_top_products') IS NOT NULL  
    DROP FUNCTION dbo.fn_top_products;  
    GO 
    
    --根据供货商ID获得单价最高的两件商品
    CREATE FUNCTION dbo.fn_top_products  
    (@supid AS INT)  
    RETURNS TABLE  
    AS  
    RETURN  
    SELECT TOP(2)Id AS ProductId,ProductName,UnitPrice  
    FROM dbo.Products  
    WHERE SupplierId = @supid  
    ORDER BY UnitPrice DESC  
    GO 
    
    --执行以下语句：
    SELECT S.id AS SupplierId,S.CompanyName,UnitPrice FROM dbo.Supplier AS S  
    OUTER APPLY dbo.fn_top_products(S.id) AS P

执行结果：  
![14224152-876aedd44cc64912b2b2ff2c1604c6cc.png][]  
注意最后为NULL的记录，reed公司因为没有商品，所以单价为NULL了。

如果用CROSS APPLY形式，执行以下查询：

SELECT S.id AS SupplierId,S.CompanyName,UnitPrice FROM dbo.Supplier AS S  
    CROSS APPLY dbo.fn_top_products(S.id) AS P

执行结果：  
![14224348-5c108e920d464cd1a2e706324b571dfc.png][]  
这样是不是OUTER APPLY和CROSS APPLY的区别更清晰了。

APPLY的执行过程，它先逻辑计算左表表达式（以上的LargeTable表），然后把右表达式（以上的自定义表值函数Split）应用到左表表达式的每一行。实际是把外部查询的列引用作为参数传递给表值函数。

4.参考博客：  
《 [【俊哥儿张】MS SQL Server：APPLY 运算符详解][MS SQL Server_APPLY] 》  
《[SQL Server中CROSS APPLY和OUTER APPLY的应用详解][SQL Server_CROSS APPLY_OUTER APPLY]》

[14222923-5abe7db7dc6e4fd19a854d5e36a30c8c.png]: /images/20220331/4f68f633640646b2a2f86c868cc28859.png
[14223038-eb6d90ef0d6d4a74b931bcaace803a64.png]: /images/20220331/bcc4dbcdb96f46e3b1967cec84826f67.png
[14231638-a009d2c05ae544bf8922ccfa147704b2.png]: /images/20220331/0d77567639bd4bd6b204327fde5f3686.png
[14223823-388065c030334cc4823e373ecebce094.png]: https://images0.cnblogs.com/blog/284255/201309/14223823-388065c030334cc4823e373ecebce094.png
[14223839-d6177fbb93e2485a97a796abd04b0af9.png]: /images/20220331/217d466ddc0b46b0851a376eb1b655a1.png
[14224152-876aedd44cc64912b2b2ff2c1604c6cc.png]: /images/20220331/af382dcff9f549dfa09cd0e23ee0bcb5.png
[14224348-5c108e920d464cd1a2e706324b571dfc.png]: /images/20220331/a1a08734afca4aba8174ed66cb05a4f2.png
[MS SQL Server_APPLY]: http://space.itpub.net/7705285/viewspace-417555
[SQL Server_CROSS APPLY_OUTER APPLY]: http://database.51cto.com/art/201108/283359.htm