SQL Server outer apply 和 cross apply

蔷薇525

　　先说点题外话，因为后面我会用到这个函数。
　　前两天自定义了一个 sql 的字符串分割函数（Split），不过后来发现有点问题，例如：
　　

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

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

USE [Test]　　
GO
　　

　　
SET ANSI_NULLS ON
　　
GO
　　
SET QUOTED_IDENTIFIER ON
　　
GO

　　
>　　
RETURNS @ResultTab TABLE (

　　
    >　　
     Res     NVARCHAR(500)
　　
)
　　
AS
　　
BEGIN
　　
     DECLARE @Num INT
　　
     DECLARE @Str nvarchar(500)
　　
     
　　
     IF(@string IS NOT NULL AND @string <> '' AND LEN(@string)>0)
　　
     BEGIN
　　
         IF(CHARINDEX(@separator,@string)>0)        
　　
         BEGIN
　　
             SET @Num=0
　　
             WHILE (CHARINDEX(@separator,@string)>0)        
　　
             BEGIN
　　
                 SET @Num=@Num+1
　　
                 set @Str=LEFT(@string,CHARINDEX(@separator,@string)-1)
　　
27                 
　　
28                 if(@Str is not null and @Str <> '')        --做一下判断，如果截取的字符串为空就不插入返回结果的表
　　
29                 begin
　　
30                     INSERT INTO @ResultTab(Id,Res)        --截取字符串，插入表变量
　　
31                     SELECT @Num,@Str
　　
32                 end
　　
33                 else
　　
34                 begin
　　
35                     set @Num=@Num-1
　　
36                 end
　　

　　
                 
　　
                 SET @string=STUFF(@string,1,CHARINDEX(@separator,@string)-1+LEN(@separator),'')   
　　
             END
　　
            
　　
            
　　
            
　　
             IF(@string IS NOT NULL AND @string <> '')
　　
             BEGIN
　　
                 INSERT INTO @ResultTab(Id,Res)
　　
                 SELECT @Num+1,@string
　　
             END            
　　
         END
　　
         ELSE
　　
         BEGIN
　　
             DELETE FROM @ResultTab
　　
         END
　　
     END
　　
     ELSE
　　
     BEGIN
　　
         DELETE FROM @ResultTab
　　
     END
　　
     RETURN
　　
END
　　

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

   select * 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 ：
　　

  select * from UserInfo u　　

   cross apply dbo.Split(default,u.P_Id)　　

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

   select 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 (　　

   select * from UserInfo u　　

   cross apply dbo.Split(default,u.P_Id))t　　

   left join OperatePower p on t.Res=p.P_Id　　

　　OUTER APPLY：
　　

   select * from UserInfo u　　

   outer apply dbo.Split(default,u.P_Id)　　

　　
   select 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 (
　　
   select * from UserInfo u
　　
   outer apply dbo.Split(default,u.P_Id))t
　　
   left join OperatePower p on t.Res=p.P_Id
　　

　　可以看到 OUTER APPLY 返回的数据比 CORSS APPLY 返回的数据要多一行，这是因为，CORSS APPLY 只是返回能够匹配上的，而 OUTER APPLY 会返回所有的，不管能不能匹配上，不能匹配的就返回空（null）。
　　当然，OUTER APPLY 和 CORSS APPLY 还可以作用于表之间的连接：
　　

create table #T(姓名 varchar(10))　　

insert into #T values('张三')　　

insert into #T values('李四')　　

insert into #T values(NULL )　　

　　
  
　　
create table #T2(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
　　
insert into #T2 values('张三' , '语文' , 74)
　　
insert into #T2 values('张三' , '数学' , 83)
　　
insert into #T2 values('张三' , '物理' , 93)
　　
insert into #T2 values(NULL , '数学' , 50)
　　

　　

　　
select * from #T a
　　
cross apply (select 课程,分数 from #t2 where 姓名=a.姓名) b
　　

　　
select * from #T a
　　
outer apply (select 课程,分数 from #t2 where 姓名=a.姓名) b
　　

　　参考：
　　http://www.cnblogs.com/qixuejia/p/3960904.html