SQL2005學習筆記 APPLY 運算符

更新時間：2009年07月19日 01:49:49 作者：

APPLY 運算符簡介： APPLY 運算符是Sql Server2005新增加的運算符。

使用APPLY運算符可以為實現(xiàn)查詢操作的外部表表達式返回的每個行調用表值函數(shù)。
表值函數(shù)作為右輸入，外部表表達式作為左輸入。
通過對右輸入求值來獲得左輸入每一行的計算結果，生成的行被組合起來作為最終輸出。
APPLY運算符生成的列的列表是左輸入中的列集，后跟右輸入返回的列的列表。
APPLY 運算符的左操作數(shù)和右操作數(shù)都是表表達式。
這些操作數(shù)之間的主要區(qū)別是:右操作數(shù)可以使用表值函數(shù)，從左操作數(shù)獲取一個列作為函數(shù)的參數(shù)之一。左操作數(shù)可以包括表值函數(shù)，但不能以來自右操作數(shù)的列作為參數(shù)。
演示一下APPLY 運算符的用法：

復制代碼代碼如下:

 
--建一個表 
CREATE TABLE MyData 
( 
ids INT IDENTITY PRIMARY KEY, 
Data NVARCHAR(1000) 
) 
go 
--插入測試數(shù)據(jù) 
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 

建立一個表,作用是：按逗號分解字符，分解出的每一個字符做一行數(shù)據(jù)返回

復制代碼代碼如下:

 
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(內部聯(lián)接)和Left Outer Join(左外部聯(lián)接)之間的關系.
其實APPLY有兩種形式：CROSS APPLY 和 OUTER APPLY。
CROSS APPLY僅返回外部表中通過表值函數(shù)生成結果集的行。
OUTER APPLY既返回生成結果集的行，也返回不生成結果集的行，其中表值函數(shù)生成的列中的值為 NULL。
以上是sql2005的解決方案，下面我演示一下sql2000怎么解決這樣的查詢：
思路是：做個循環(huán)來逐個鏈接查詢。

復制代碼代碼如下:

 
--sql2000版本 
declare @ids int 
select @ids =0 
declare @data nvarchar(200) 
select @data='' 
--定義表變量臨時存放數(shù)據(jù) 
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 