SQL Server 关于CROSS APPLY 和 OUTER APPLY应用
扫描二维码
随时随地手机看文章
先看看语法：
再让我们了解一下apply运算涉及的两个步骤：
A1：把右表表达式(
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.付款额 = 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
下载文档
