SQL2005學習筆記 APPLY 運算符

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

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

使用APPLY運算符可以為實現查詢操作的外部表表達式返回的每個行調用表值函數。
表值函數作為右輸入，外部表表達式作為左輸入。
通過對右輸入求值來獲得左輸入每一行的計算結果，生成的行被組合起來作為最終輸出。
APPLY運算符生成的列的列表是左輸入中的列集，后跟右輸入返回的列的列表。
APPLY 運算符的左操作數和右操作數都是表表達式。
這些操作數之間的主要區(qū)別是:右操作數可以使用表值函數，從左操作數獲取一個列作為函數的參數之一。左操作數可以包括表值函數，但不能以來自右操作數的列作為參數。
演示一下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(內部聯(lián)接)和Left Outer Join(左外部聯(lián)接)之間的關系.
其實APPLY有兩種形式：CROSS APPLY 和 OUTER APPLY。
CROSS APPLY僅返回外部表中通過表值函數生成結果集的行。
OUTER APPLY既返回生成結果集的行，也返回不生成結果集的行，其中表值函數生成的列中的值為 NULL。
以上是sql2005的解決方案，下面我演示一下sql2000怎么解決這樣的查詢：
思路是：做個循環(huán)來逐個鏈接查詢。

復制代碼代碼如下:

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