SQL Server中CROSS APPLY和OUTER APPLY应用

1.Cross ApplyOuter Apply的理解

新增的APPLY表运算符把右表表达式应用到左表表达式中的每一行。
它不像JOIN那样先计算哪个表表达式都可以,APPLY必选先逻辑地计算左表达式。这种计算输入的逻辑顺序允许把右表达式关联到左表表达式。

APPLY有两种形式,一个是OUTER APPLY,一个是CROSS APPLYCROSS APPLY仅返回外部表中通过表值函数生成结果集的行。OUTER APPLY既返回生成结果集的行,也返回不生成结果集的行,其中表值函数生成的列中的值为 NULL

语法:
APPLY 操作符作用于 FROM 字句中,它的具体语法如下:

FROM left_table_source    
  { CROSS | OUTER } APPLY right_table_source

在上面的语法中,left_table_source 表示坐输入;right_table_source 表示右输入,一般是表值函数,但亦可是视图、表、派生表。

2.示例测试

(1).CROSS APPLY

说明:只返回外部表中包含的数据行,这些航从表值函数中生成结果集。 比如:LargeTable表中的某一列存储的数据是以“:”号分隔的数据,我们处理的时候,可能要先把这个值,先分隔,然后把分隔后的每个值单独一行放在一张表中,然后对这个表做处理。原始数据(LargeTable表):
在这里插入图片描述
为了简单,先用其中id=2的一行处理,这些以:号分隔的数据,可能是我们某张表的主键(t1),我们可能需要把这些数值提出来,放在一张临时表中,和t1表关联,做一些处理。处理分隔数据效果图:
在这里插入图片描述

如果用之前的版本处理这个操作的话,应该复杂些。这只是用其中一行做的处理,如果我们用上图的3行都做这样处理,把三行以:号分隔的数值都放在一个表中,该怎么处理呢?

APPLY表运算符一行语句就能简单的处理以上操作:

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

执行结果:
在这里插入图片描述

需要额外定义的就是那个自定义表值函数(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),我们要取每一个供货商中单价最高的两个产品。其表数据:
在这里插入图片描述
在这里插入图片描述

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

执行结果:
在这里插入图片描述
注意最后为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 

执行结果:
在这里插入图片描述
这样是不是OUTER APPLYCROSS APPLY的区别更清晰了。

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

  • 1
    点赞
  • 12
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值