SQL2005學習筆記 APPLY 運算符

2024-08-31 01:01:32

字體：大中小

來源：轉載

供稿：網友

使用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版本
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