SQL Server outer apply 和 cross apply

青春须早为，岂能长少年。这篇文章主要讲述SQL Server outer apply 和 cross apply相关的知识，希望能为你提供帮助。
先说点题外话，因为后面我会用到这个函数。
前两天自定义了一个 sql 的字符串分割函数（Split），不过后来发现有点问题，例如：

1select * from Split(default,\'123,456,,,,789,\')

文章图片

我之前只处理了截取的最后一个为空的字符串，所以会出现以上的结果，现在我做了一些修改。代码如下：

文章图片

1 USE [Test] 2 GO 3 /****** Object:UserDefinedFunction [dbo].[Split]Script Date: 2017/4/16 22:05:35 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 ALTER FUNCTION [dbo].[Split](@separator VARCHAR(64)=\',\',@string NVARCHAR(max)) 9 RETURNS @ResultTab TABLE ( 10IdINT , 11ResNVARCHAR(500) 12 ) 13 AS 14 BEGIN 15DECLARE @Num INT 16DECLARE @Str nvarchar(500) 17 18IF(@string IS NOT NULL AND @string < > \'\' AND LEN(@string)> 0) 19BEGIN 20IF(CHARINDEX(@separator,@string)> 0)--判断要截取的字符是否存在 21BEGIN 22SET @Num=0 23WHILE (CHARINDEX(@separator,@string)> 0)--如果要截取的字符存在，就继续循环 24BEGIN 25SET @Num=@Num+1 26set @Str=LEFT(@string,CHARINDEX(@separator,@string)-1) 27 28if(@Str is not null and @Str < > \'\')--做一下判断，如果截取的字符串为空就不插入返回结果的表 29begin 30INSERT INTO @ResultTab(Id,Res)--截取字符串，插入表变量 31SELECT @Num,@Str 32end 33else 34begin 35set @Num=@Num-1 36end 37 38--把已经截取并插入的字符串删除 39SET @string=STUFF(@string,1,CHARINDEX(@separator,@string)-1+LEN(@separator),\'\') 40END 41 42--如果最后一个截取的字符串为空，那就不插入了 43--例如：\'123，456，789，\' 这样的字符串最后剩下的就是空字符串了 44IF(@string IS NOT NULL AND @string < > \'\') 45BEGIN 46INSERT INTO @ResultTab(Id,Res) 47SELECT @Num+1,@string 48END 49END 50ELSE 51BEGIN 52DELETE FROM @ResultTab 53END 54END 55ELSE 56BEGIN 57DELETE FROM @ResultTab 58END 59RETURN 60 END

文章图片
红色部分的代码为添加或修改的部分，下面再看一下效果。

1select * from Split(default,\'123,456,,,,789,321,,,\')

文章图片

之前自定义 Split 函数时我还觉得不能直接作用于表，不过今天了解到一个方法，让我觉得或许可以实现。
APPLY 运算符：
使用 APPLY 运算符(2005或以上版本)可以为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函数作为右输入，外部表表达式作为左输入。通过对右输入求值来获得左输入每一行的计算结果，生成的行被组合起来作为最终输出。APPLY 运算符生成的列的列表是左输入中的列集，后跟右输入返回的列的列表。
APPLY 有两种形式： CROSS APPLY 和 OUTER APPLY。CROSS APPLY 仅返回外部表中通过表值函数生成结果集的行。OUTER APPLY 既返回生成结果集的行，也返回不生成结果集的行，其中表值函数生成的列中的值为 NULL。
语法：
< left_table_expression> {cross|outer} apply < right_table_expression>

先看看示例所用的数据：

文章图片

文章图片

现在有两个表，一个用户信息表和一个操作权限表，下面通过示例看看 apply 运算符有什么作用。
CROSS APPLY ：

1select * from UserInfo u 2cross apply dbo.Split(default,u.P_Id)

【SQL Server outer apply 和 cross apply】

文章图片

最后两列为使用 cross apply 连接表值函数 Split 分割字段 P_Id 的值。下面如果我们要查询操作权限的名称呢？

1select t.U_Id,t.U_No,t.U_Name,t.U_Pwd,t.P_Id,p.P_Id,p.P_Name,p.P_Remark from ( 2select * from UserInfo u 3cross apply dbo.Split(default,u.P_Id))t 4left join OperatePower p on t.Res=p.P_Id

文章图片

OUTER APPLY：

文章图片

1select * from UserInfo u 2outer apply dbo.Split(default,u.P_Id) 3 4select t.U_Id,t.U_No,t.U_Name,t.U_Pwd,t.P_Id,p.P_Id,p.P_Name,p.P_Remark from ( 5select * from UserInfo u 6outer apply dbo.Split(default,u.P_Id))t 7left join OperatePower p on t.Res=p.P_Id

文章图片

文章图片

可以看到 OUTER APPLY 返回的数据比 CORSS APPLY 返回的数据要多一行，这是因为，CORSS APPLY 只是返回能够匹配上的，而 OUTER APPLY 会返回所有的，不管能不能匹配上，不能匹配的就返回空（null）。

当然，OUTER APPLY 和 CORSS APPLY 还可以作用于表之间的连接：

文章图片

1 create table #T(姓名 varchar(10)) 2 insert into #T values(\'张三\') 3 insert into #T values(\'李四\') 4 insert into #T values(NULL ) 5 6 7 create table #T2(姓名 varchar(10) , 课程 varchar(10) , 分数 int) 8 insert into #T2 values(\'张三\' , \'语文\' , 74) 9 insert into #T2 values(\'张三\' , \'数学\' , 83) 10 insert into #T2 values(\'张三\' , \'物理\' , 93) 11 insert into #T2 values(NULL , \'数学\' , 50) 12 13 14 select * from #T a 15 cross apply (select 课程,分数 from #t2 where 姓名=a.姓名) b 16 17 select * from #T a 18 outer apply (select 课程,分数 from #t2 where 姓名=a.姓名) b

文章图片