使用 APPLY 運算符可以為實現查詢操作的外部表表達式返回的每個行調用表值函數。表值函數作為右輸入，外部表表達式作為左輸入。通過對右輸入求值來獲得左輸入每一行的計算結果，生成的行被組合起來作為最終輸出。 APPLY 運算符生成的列的列表是左輸入中的列集，后跟右輸入返回的列的列表。

APPLY 有兩種形式： CROSS APPLY 和 OUTER APPLY 。 CROSS APPLY 僅返回外部表中通過表值函數生成結果集的行。 OUTER APPLY 既返回生成結果集的行，也返回不生成結果集的行，其中表值函數生成的列中的值為 NULL 。

-- 以上是 SQLServer 2005 幫助中的講解 , 下面還是看個例子吧

-- apply 運算符的主要用途就是和表值函數配合 , 用來替代 SQLServer 2000 中的游標

--Create Employees table and insert values

-- 員工表 共四列 員工 id 部門主管 id 員工姓名 傭金

CREATE TABLE Employees

(

empid int NOT NULL,

mgrid int NULL,

empname varchar ( 25 ) NOT NULL,

salary money NOT NULL,

CONSTRAINT PK_Employees PRIMARY KEY ( empid ),

)

GO

INSERT INTO Employees VALUES ( 1 , NULL, 'Nancy' , $10000.00 )

INSERT INTO Employees VALUES ( 2 , 1 , 'Andrew' , $5000.00 )

INSERT INTO Employees VALUES ( 3 , 1 , 'Janet' , $5000.00 )

INSERT INTO Employees VALUES ( 4 , 1 , 'Margaret' , $5000.00 )

INSERT INTO Employees VALUES ( 5 , 2 , 'Steven' , $2500.00 )

INSERT INTO Employees VALUES ( 6 , 2 , 'Michael' , $2500.00 )

INSERT INTO Employees VALUES ( 7 , 3 , 'Robert' , $2500.00 )

INSERT INTO Employees VALUES ( 8 , 3 , 'Laura' , $2500.00 )

INSERT INTO Employees VALUES ( 9 , 3 , 'Ann' , $2500.00 )

INSERT INTO Employees VALUES ( 10 , 4 , 'Ina' , $2500.00 )

INSERT INTO Employees VALUES ( 11 , 7 , 'David' , $2000.00 )

INSERT INTO Employees VALUES ( 12 , 7 , 'Ron' , $2000.00 )

INSERT INTO Employees VALUES ( 13 , 7 , 'Dan' , $2000.00 )

INSERT INTO Employees VALUES ( 14 , 11 , 'James' , $1500.00 )

GO

--Create Departments table and insert values

-- 部門表 共散列 部門 id 部門名稱 外鍵部門主管 id

CREATE TABLE Departments

(

deptid INT NOT NULL PRIMARY KEY ,

deptname VARCHAR ( 25 ) NOT NULL,

deptmgrid INT NULL REFERENCES Employees

)

GO

INSERT INTO Departments VALUES ( 1 , 'HR' , 2 )

INSERT INTO Departments VALUES ( 2 , 'Marketing' , 7 )

INSERT INTO Departments VALUES ( 3 , 'Finance' , 8 )

INSERT INTO Departments VALUES ( 4 , 'R&D' , 9 )

INSERT INTO Departments VALUES ( 5 , 'Training' , 4 )

INSERT INTO Departments VALUES ( 6 , 'Gardening' , NULL)

Go

select * from employees

select * from Departments

結果 :

-- 表值函數 根據部門主管 id 查詢出該部門主管下屬員工

--with 是 CTE 語法 , 不了解的先查詢 SQLServer 2005 幫助

CREATE FUNCTION dbo . fn_getsubtree ( @empid AS INT ) RETURNS @TREE TABLE

(

empid INT NOT NULL,

empname VARCHAR ( 25 ) NOT NULL,

mgrid INT NULL,

lvl INT NOT NULL

)

AS

BEGIN

WITH Employees_Subtree ( empid , empname , mgrid , lvl )

AS

(

-- Anchor Member (AM)

SELECT empid , empname , mgrid , 0

FROM employees

WHERE empid = @empid

UNION all

-- Recursive Member (RM)

SELECT e . empid , e . empname , e . mgrid , es . lvl + 1

FROM employees AS e

JOIN employees_subtree AS es

ON e . mgrid = es . empid

)

INSERT INTO @TREE

SELECT * FROM Employees_Subtree

RETURN

END

GO

-- 根據上面的表值函數我們可以很方便的查詢出某個部門主管的下屬都是誰

-- 但是 , 如果查詢出所有部門主管的下屬就麻煩了 , 需要使用游標

-- 定義表變量臨時存放數據

declare @tem table (

empid int ,

mgrid int ,

empname varchar ( 25 ),

lvl int ,

deptid INT ,

deptname VARCHAR ( 25 ),

deptmgrid INT

)

DECLARE @ids int

DECLARE test_cursor CURSOR FOR

select deptmgrid FROM Departments

OPEN test_cursor

FETCH NEXT FROM test_cursor

INTO @ids

WHILE @@FETCH_STATUS = 0

begin

insert into @tem select empid , mgrid , empname , lvl , deptid , deptname , deptmgrid from dbo . fn_getsubtree ( @ids ) left join Departments on deptmgrid = @ids

FETCH NEXT FROM test_cursor

INTO @ids

end

CLOSE test_cursor

DEALLOCATE test_cursor

select * from @tem

結果 :

-- 游標效率太差 , 萬不得已不要使用 ,SQLServer2005 的 apply 運算符可以替代它

SELECT *

FROM Departments AS D

CROSS APPLY fn_getsubtree ( D . deptmgrid ) AS ST

SELECT *

FROM Departments AS D

OUTER APPLY fn_getsubtree ( D . deptmgrid ) AS ST

結果 :

===================================================

最后一行數據體現出 CROSS APPLY和 OUTER APPLY的不同,這有點類似Inner join和left join的區別.

好吧,我承認我是標題黨,apply配合表值函數的用法,只能替代只進游標,但是這種方法效率要比游標好的多.

SQLServer 2005 和游標cursor說再見——apply運算符