SQL Server 2005 APPLY 运算符

APPLY运算符是Sql Server2005新增加的运算符。
 
　　使用APPLY运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。
 
　　表值函数作为右输入，外部表表达式作为左输入。
 
　　通过对右输入求值来获得左输入每一行的计算结果，生成的行被组合起来作为最终输出。
 
　　APPLY运算符生成的列的列表是左输入中的列集，后跟右输入返回的列的列表。
 
　　APPLY 运算符的左操作数和右操作数都是表表达式。
 
　　这些操作数之间的主要区别是:右操作数可以使用表值函数，从左操作数获取一个列作为函数的参数之一。左操作数可以包括表值函数，但不能以来自右操作数的列作为参数。
 
　　演示一下APPLY 运算符的用法：
 
　　--建一个表
 
　  --插入测试数据
 

 

 

 insert INTO MyData VALUES(’’) 
　　insert INTO MyData VALUES(’a,b,c’) 
　　insert INTO MyData VALUES(’q’) 
　　insert INTO MyData VALUES(’i,p’) 
　　GO 
　　select * from MyData 
　　go

　　--查询结果
 
  ids Data 
　　1 
　　2 a,b,c 
　　3 q 
　　4 i,p

　　建立一个表,作用是：按逗号分解字符，分解出的每一个字符做一行数据返回
 

   create FUNCTION fun_MyData( 
　　@data AS NVARchar(1000) 
　　) 
　　RETURNS @tem TABLE( id INT , value nvarchar(100) ) 
　　AS 
　　BEGIN 
　　select @data=isnull(@data,’’) 
　　if len(@data)=0 
　　return --字符长度为0 ，退出 
　　declare @id AS INT 
　　select @id=1 
　　declare @end AS INT 
　　select @end = charINDEX(’,’, @data) 
　　while(@end>0) 
　　begin 
　　insert into @tem(id,value) 
　　select @id,left(@data,@end-1) 
　　select @id=@id+1 
　　select @data=right(@data,len(@data)-@end) 
　　select @end = charINDEX(’,’, @data) 
　　end 
　　if len(@data)>0 
　　begin 
　　insert into @tem(id,value) 
　　select @id,@data 
　　end 
　　RETURN 
　　END

 

开始使用APPLY 运算符：
 
     select m.ids, f.*
     FROM MyData m CROSS APPLY fun_MyData(data) f
     go

　　--结果
 
　　ids id value
 
　　2 1 a
       2 2 b
　　2 3 c
　　3 1 q
　　4 1 i
　　4 2 p
 
    
 select m.ids, f.* 
     FROM MyData m OUTER APPLY fun_MyData(data) f 
     g o

　　--结果
 
　　ids id value
 
　　1 NULL NULL
　　2 1 a
　　2 2 b
　　2 3 c
　　3 1 q
　　4 1 i
　　4 2 p
 
　　我们看到OUTER APPLY返回的结果行比CROSS APPLY多。
　　这一点有点象inner join(内部联接)和Left Outer join(左外部联接)之间的关系.
　　其实APPLY有两种形式：CROSS APPLY 和 OUTER APPLY。
　　CROSS APPLY仅返回外部表中通过表值函数生成结果集的行。
　　OUTER APPLY既返回生成结果集的行，也返回不生成结果集的行，其中表值函数生成的列中的值为 NULL。
　　以上是sql2005的解决方案，下面我演示一下sql2000怎么解决这样的查询：
　　思路是：做个循环来逐个链接查询。
　　--sql2000版本 

declare @ids int 
　　select @ids =0 
　　declare @data nvarchar(200) 
　　select @data=’’ 
　　--定义表变量临时存放数据 
　　declare @tem table( 
　　ids int, 
　　id int, 
　　value nvarchar(100) 
　　) 
　　DECLARE test_cursor CURSOR FOR 
　　select ids, Data FROM MyData 
　　OPEN test_cursor 
　　FETCH NEXT FROM test_cursor 
　　INTO @ids,@data 
　　WHILE @@FETCH_STATUS = 0 
　　begin 
　　insert into @tem 
　　select @ids,id,value 
　　from dbo.fun_MyData(@data) 
　　FETCH NEXT FROM test_cursor 
　　INTO @ids,@data 
　　end 
　　CLOSE test_cursor 
　　DEALLOCATE test_cursor 
　　select * from @tem

 

　　同样得到了结果，但是sql2000要利用循环，这样代码复杂，计算耗时。
 
　　让我们充分利用Sql Server2005新兵器：APPLY运算符给我们带来的简便快捷的运算方式吧.