• SQL Server outer apply 和 cross apply


    先说点题外话,因为后面我会用到这个函数。

    前两天自定义了一个 sql 的字符串分割函数(Split),不过后来发现有点问题,例如:

    1   select * 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 (
    10     Id    INT ,
    11     Res     NVARCHAR(500) 
    12 )
    13 AS
    14 BEGIN
    15     DECLARE @Num INT
    16     DECLARE @Str nvarchar(500)
    17     
    18     IF(@string IS NOT NULL AND @string <> '' AND LEN(@string)>0)
    19     BEGIN
    20         IF(CHARINDEX(@separator,@string)>0)        --判断要截取的字符是否存在
    21         BEGIN
    22             SET @Num=0
    23             WHILE (CHARINDEX(@separator,@string)>0)        --如果要截取的字符存在,就继续循环
    24             BEGIN
    25                 SET @Num=@Num+1
    26                 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
    37 
    38                 --把已经截取并插入的字符串删除
    39                 SET @string=STUFF(@string,1,CHARINDEX(@separator,@string)-1+LEN(@separator),'')    
    40             END
    41             
    42             --如果最后一个截取的字符串为空,那就不插入了
    43             --例如:'123,456,789,' 这样的字符串最后剩下的就是空字符串了
    44             IF(@string IS NOT NULL AND @string <> '')
    45             BEGIN
    46                 INSERT INTO @ResultTab(Id,Res)
    47                 SELECT @Num+1,@string 
    48             END            
    49         END
    50         ELSE
    51         BEGIN
    52             DELETE FROM @ResultTab
    53         END
    54     END
    55     ELSE
    56     BEGIN
    57         DELETE FROM @ResultTab
    58     END
    59     RETURN
    60 END

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

    1   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 :

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

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

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

    OUTER APPLY:

    1   select * from UserInfo u
    2   outer apply dbo.Split(default,u.P_Id)
    3 
    4   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 (
    5   select * from UserInfo u
    6   outer apply dbo.Split(default,u.P_Id))t
    7   left 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

    参考:

    http://www.cnblogs.com/qixuejia/p/3960904.html

  • 相关阅读:
    Spring-security+Oauth2.0 零散知识汇总(备忘)
    Java Mail 发送邮件 JMail 多人 解决乱码 带附件
    AngularJs 性能优化英文原版(个人备份使用)
    Java基础练习- Java-Swing 拼图游戏 第一部分:创建java窗口添加窗口元素
    Json格式页面解析不出来
    spring-security 异常处理求助
    MySQL-存储过程
    吸血鬼数字
    浏览器的兼容性
    es6-----promise
  • 原文地址:https://www.cnblogs.com/Brambling/p/6720875.html
Copyright © 2020-2023  润新知