SQL Server 关于CROSS APPLY 和 OUTER APPLY应用

先看看语法：

<left_table_expression>  {cross|outer} apply<right_table_expression>

再让我们了解一下apply运算涉及的两个步骤：

A1：把右表表达式(<right_table_expression>)应用到左表(<left_table_expression>)输入的行

A2：添加外部行；

　　使用apply就像是先计算左输入，然后为左输入中的每一行计算一次右输入。（这一句很重要，可能会不理解，但要先记住，后面会有详细的说明）

最后结合以上两个步骤说明cross apply和outer apply的区别：

　　crossapply和outer apply 总是包含步骤A1，只有outer apply包含步骤A2，如果crossapply左行应用右表表达式时返回空积，则不返回该行。而outer apply返回该行，并且该行的右表表达式的属性为null。

看到上面的解释或步骤大家可能还是一头的雾水，不知所云。

下面用例子来说明：

--表一（[dbo].[Customers]  字段说明：customerid--消费者id， city -- 所在城市）：
CREATE TABLE [dbo].[Customers](
    [customerid][char](5)NOTNULL,
    [city][varchar](10)NOTNULL,
PRIMARY KEY CLUSTERED ( [customerid]ASC)WITH(IGNORE_DUP_KEY=OFF)ON [PRIMARY]
) ON [PRIMARY]
--向表一插入数据：
insert into dbo.Customersvalues('FISSA','Madrid');
insert into dbo.Customersvalues('FRNDO','Madrid');
insert into dbo.Customersvalues('KRLOS','Madrid');
insert into dbo.Customersvalues('MRPHS','Zion');
--表二（[dbo].[Orders]  字段说明：orderid--订单id ，customerid -- 消费者id）：
CREATE TABLE [dbo].[Orders](
    [orderid] [int]NOT NULL,
    [customerid][char](5)NULL,
PRIMARY KEY CLUSTERED ([orderid]ASC)WITH(IGNORE_DUP_KEY=OFF)ON [PRIMARY]
) ON [PRIMARY]
--向表二插入数据：
insert into dbo.Ordersvalues(1,'FRNDO');
insert into dbo.Ordersvalues(2,'FRNDO');
insert into dbo.Ordersvalues(3,'KRLOS');
insert into dbo.Ordersvalues(4,'KRLOS');
insert into dbo.Ordersvalues(5,'KRLOS');
insert into dbo.Ordersvalues(6,'MRPHS');
insert into dbo.Ordersvalues(7,null);
--查询插入的数据：
select *from dbo.Customers
select *from dbo.orders

结果：
customerid city
---------- ----------
FISSA      Madrid
FRNDO      Madrid
KRLOS      Madrid
MRPHS      Zion
 
(4 行受影响)
 
orderid     customerid
----------- ----------
1           FRNDO
2           FRNDO
3           KRLOS
4           KRLOS
5           KRLOS
6           MRPHS
7           NULL
 
(7 行受影响)

 

【例子】得到每个消费者最新的两个订单：

1、用cross apply

SELECT  *
FROM    dbo.CustomersAS A
        CROSS APPLY( SELECT TOP 2 *
                     FROM      dbo.OrdersAS B
                     WHERE     A.customerid= B.customerid
                     ORDER BY  orderid DESC
                    ) AS AB;

结果：

customerid city       orderid     customerid
---------- ---------- ---------------------
FRNDO      Madrid    2           FRNDO
FRNDO      Madrid    1           FRNDO
KRLOS      Madrid    5           KRLOS
KRLOS      Madrid    4           KRLOS
MRPHS     Zion       6           MRPHS

过程分析：

　　它是先得出左表【dbo.Customers】里的数据，然后把此数据一条一条的放入右表表式中，分别得出结果集，最后把结果集整合到一起就是最终的返回结果集了（T1的数据 像for循环一样 一条一条的进入到T2中 然后返回一个集合 最后把所有的集合整合到一块 就是最终的结果），最后我们再理解一下上面让记着的话（使用apply就像是先计算左输入，让后为左输入中的每一行计算一次右输入）。

2、用outer apply

SELECT  *
FROM    dbo.CustomersAS A
        OUTER APPLY( SELECT TOP 2
                                *
                     FROM      dbo.OrdersAS B
                     WHERE     A.customerid= B.customerid
                     ORDER BY  orderid DESC
                    ) AS AB;

结果：

customerid city       orderid     customerid
---------- ---------- ---------------------
FISSA      Madrid    NULL        NULL
FRNDO      Madrid    2           FRNDO
FRNDO      Madrid    1           FRNDO
KRLOS      Madrid    5           KRLOS
KRLOS      Madrid    4           KRLOS
MRPHS     Zion       6           MRPHS

结果分析：

　　发现outerapply得到的结果比cross多了一行，我们结合上面所写的区别（cross apply和outer apply 总是包含步骤A1，只有outer apply包含步骤A2，如果cross apply左行应用右表表达式时返回空积，则不返回该行。而outerapply返回改行，并且改行的右表表达式的属性为null）就会知道了。

【例二】

;with tb1(客户号,销售日期,销售额)AS(
    select '001','2017-05-01',460 union all
    select '001','2017-05-02',240 union  all
    select '001','2017-05-03',300 
),TB2(客户号,付款额)AS(
    select  '001',500
)
SELECT  t1.客户号 ,
        t1.销售日期 ,
        t1.销售额 ,
        CASE WHEN t2.付款额 <= ISNULL(tt1.p_销售额, 0) THEN 0
             ELSE CASE WHEN ( ISNULL(p_销售额, 0) + t1.销售额 ) >= t2.付款额
                       THEN t2.付款额 - ISNULL(p_销售额, 0)
                       ELSE t1.销售额
                  END
        END AS 实付额
FROM    tb1 AS t1
        CROSS APPLY ( SELECT    SUM(付款额) AS 付款额
                      FROM      TB2
                      WHERE     TB2.客户号 = t1.客户号
                    ) AS t2
        OUTER APPLY ( SELECT    SUM(销售额) AS p_销售额
                      FROM      tb1 AS tt
                      WHERE     tt.客户号 = t1.客户号
                                AND tt.销售日期 < t1.销售日期
                    ) AS tt1;

--结果
客户号  销售日期       销售额         实付额
---- ---------- ----------- -----------
001  2017-05-01 460         460
001  2017-05-02 240         40
001  2017-05-03 300         0