Sql Server2005 Transact-SQL 新兵器学习总结之-APPLY 运算符

APPLY 运算符简介：
APPLY 运算符是Sql Server2005新增加的运算符。

使用APPLY运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。
表值函数作为右输入，外部表表达式作为左输入。
通过对右输入求值来获得左输入每一行的计算结果，生成的行被组合起来作为最终输出。
APPLY运算符生成的列的列表是左输入中的列集，后跟右输入返回的列的列表。

APPLY 运算符的左操作数和右操作数都是表表达式。
这些操作数之间的主要区别是:右操作数可以使用表值函数，从左操作数获取一个列作为函数的参数之一。左操作数可以包括表值函数，但不能以来自右操作数的列作为参数。

演示一下APPLY 运算符的用法：

-- 建一个表
CREATE   TABLE  MyData
(
  ids  INT   IDENTITY   PRIMARY   KEY ,
  Data  NVARCHAR ( 1000 ) 
)
go

-- 插入测试数据
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
go
-- 结果
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版本
--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运算符给我们带来的简便快捷的运算方式吧.