SQL Server连接查询之Cross Apply和Outer Apply的区别及用法(转载)

先简单了解下cross apply的语法以及会产生什么样的结果集吧!
示例表:

SQL Server连接查询之Cross Apply和Outer Apply的区别及用法(转载)SQL Server连接查询之Cross Apply和Outer Apply的区别及用法(转载)

 

SELECT * FROM tableA CROSS APPLY tableB

两张表直接连接,不需要任何的关联条件,产生的结果就是这两张表的笛卡儿集

SQL Server连接查询之Cross Apply和Outer Apply的区别及用法(转载)

相当于:select * from tableA,tableB
与之对应的还有Out Apply,下面讲解一下Cross Apply 和 Outer Apply 的区别:
首先是Cross Apply:

SELECT * FROM tableA a CROSS APPLY (select * from tableB where id=a.id) b

SQL Server连接查询之Cross Apply和Outer Apply的区别及用法(转载)

这个结果集和 select * from tableA a inner join tableB b on a.id=b.id 一模一样,这就相当于inner join 的连接查询嘛!当然,你也可以这样写:

SELECT * FROM tableA a CROSS APPLY tableB b
WHERE a.id=b.id

结果集一模一样无差别!
其次是Outer Apply:

SELECT * FROM tableA a OUTER APPLY (select * from tableB where id=a.id) b

SQL Server连接查询之Cross Apply和Outer Apply的区别及用法(转载)

发现了吧!这个结果集和left join连接查询产生的结果集一模一样~
同时,发现Cross Apply 和 Cross JOin 的区别了没?
Cross Apply 可以在关联表子查询中用前一个关联表的字段的值,而Cross Join 却不行,比如这样写:SELECT * FROM tableA a CROSS JOIN (select * from tableB where id=a.id) b,语法上就不能通过!
因此Cross Join 很鸡肋,既然有Cross Apply了,那么Cross Join几乎毫无存在的价值~

 

 

APPLY使用举例 


 

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

 

例1:CROSS APPLY 形式

比如:LargeTable表中的某一列存储的数据是以“:”号分隔的数据,我们处理的时候,可能要先把这个值,先分隔,然后把分隔后的每个值单独一行放在一张表中,然后对这个表做处理。
原始数据(LargeTable表):

SQL Server连接查询之Cross Apply和Outer Apply的区别及用法(转载)

为了简单,我们先拿其中id=2的一行处理,这些以:号分隔的数据,可能是我们某张表的主键(t1),我们可能需要把这些数值提出来,放在一张临时表中,和t1表关联,做一些处理。
处理这个分隔的数据结果如下图:

SQL Server连接查询之Cross Apply和Outer Apply的区别及用法(转载)

如果用之前的版本处理这个操作的话,应该很发杂,暂时没想到怎么处理,如果有人实现过,可以提示一下。
这只是用其中一行做的处理,如果我们用上图的3行都做这样处理,把三行以:号分隔的数值都放在一个表中,该怎么处理呢?
今天的主角APPLY该闪亮登场了。用APPLY表运算符一行语句就能处理以上操作。

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

处理的结果如下图:

SQL Server连接查询之Cross Apply和Outer Apply的区别及用法(转载)

是不是很简单。
需要额外定义的就是那个自定义表值函数(split),这是我在网上找的,类似.Net中Split操作,代码如下:
使用方法:

SELECT * FROM dbo.split(581::579::519::279::406::361::560,:)  

ALTER 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 

 

例2:OUTER APPLY 形式

场景:有个供货商表(Supplier)和供货商产品表(Products),我们要取每一个供货商中单价最高的两个产品。
供货商表:

SQL Server连接查询之Cross Apply和Outer Apply的区别及用法(转载)

供货商产品表:

SQL Server连接查询之Cross Apply和Outer Apply的区别及用法(转载)

首先,我们创建一个自定义表值函数(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 

执行结果如下:

SQL Server连接查询之Cross Apply和Outer Apply的区别及用法(转载)

注意最后为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 

生成的输出结果如下:

SQL Server连接查询之Cross Apply和Outer Apply的区别及用法(转载)

大家看出OUTER APPLY和CROSS APPLY的区别了吧。
再次说一下APPLY的执行过程,它先逻辑计算左表表达式(以上的LargeTable表),然后把右表达式(以上的自定义表值函数Split)应用到左表表达式的每一行。实际是把外部查询的列引用作为参数传递给表值函数。

 

 

参考文献:

SQLServer连接查询之Cross Apply和Outer Apply的区别及用法

SQL Server中CROSS APPLY和OUTER APPLY的应用详解

 

SQL Server连接查询之Cross Apply和Outer Apply的区别及用法(转载)

上一篇:iOS7 自己定义动画跳转


下一篇:安卓9.0系统机器(亲测有效)激活Xposed框架的步骤