快捷導(dǎo)航

SQL2005學(xué)習(xí)筆記 APPLY 運(yùn)算符

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

APPLY 運(yùn)算符簡(jiǎn)介： APPLY 運(yùn)算符是Sql Server2005新增加的運(yùn)算符。

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

復(fù)制代碼代碼如下:

 
--建一個(gè)表 
CREATE TABLE MyData 
( 
ids INT IDENTITY PRIMARY KEY, 
Data NVARCHAR(1000) 
) 
go 
--插入測(cè)試數(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 
--查詢結(jié)果 
ids Data 
1 
2 a,b,c 
3 q 
4 i,p 

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

復(fù)制代碼代碼如下:

 
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 --字符長(zhǎng)度為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 運(yùn)算符：

復(fù)制代碼代碼如下:

 
SELECT m.ids, f.* 
FROM MyData m CROSS APPLY fun_MyData(data) f 
go 
--結(jié)果 
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 
--結(jié)果 
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返回的結(jié)果行比CROSS APPLY多。
這一點(diǎn)有點(diǎn)象inner join(內(nèi)部聯(lián)接)和Left Outer Join(左外部聯(lián)接)之間的關(guān)系.
其實(shí)APPLY有兩種形式：CROSS APPLY 和 OUTER APPLY。
CROSS APPLY僅返回外部表中通過表值函數(shù)生成結(jié)果集的行。
OUTER APPLY既返回生成結(jié)果集的行，也返回不生成結(jié)果集的行，其中表值函數(shù)生成的列中的值為 NULL。
以上是sql2005的解決方案，下面我演示一下sql2000怎么解決這樣的查詢：
思路是：做個(gè)循環(huán)來(lái)逐個(gè)鏈接查詢。

復(fù)制代碼代碼如下:

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