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

     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运算符给我们带来的简便快捷的运算方式吧.



  blog:
  http://www.cnblogs.com/aierong
  author:aierong
  email:aierong@126.com