使用 APPLY 運(yùn)算符可以為實(shí)現(xiàn)查詢(xún)操作的外部表表達(dá)式返回的每個(gè)行調(diào)用表值函數(shù)。表值函數(shù)作為右輸入，外部表表達(dá)式作為左輸入。通過(guò)對(duì)右輸入求值來(lái)獲得左輸入每一行的計(jì)算結(jié)果，生成的行被組合起來(lái)作為最終輸出。 APPLY 運(yùn)算符生成的列的列表是左輸入中的列集，后跟右輸入返回的列的列表。

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

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

-- apply 運(yùn)算符的主要用途就是和表值函數(shù)配合 , 用來(lái)替代 SQLServer 2000 中的游標(biāo)

--Create Employees table and insert values

-- 員工表 共四列 員工 id 部門(mén)主管 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

-- 部門(mén)表 共散列 部門(mén) id 部門(mén)名稱(chēng) 外鍵部門(mén)主管 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

結(jié)果 :

-- 表值函數(shù) 根據(jù)部門(mén)主管 id 查詢(xún)出該部門(mén)主管下屬員工

--with 是 CTE 語(yǔ)法 , 不了解的先查詢(xún) 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

-- 根據(jù)上面的表值函數(shù)我們可以很方便的查詢(xún)出某個(gè)部門(mén)主管的下屬都是誰(shuí)

-- 但是 , 如果查詢(xún)出所有部門(mén)主管的下屬就麻煩了 , 需要使用游標(biāo)

-- 定義表變量臨時(shí)存放數(shù)據(jù)

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

結(jié)果 :

-- 游標(biāo)效率太差 , 萬(wàn)不得已不要使用 ,SQLServer2005 的 apply 運(yùn)算符可以替代它

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

結(jié)果 :

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

最后一行數(shù)據(jù)體現(xiàn)出 CROSS APPLY和 OUTER APPLY的不同,這有點(diǎn)類(lèi)似Inner join和left join的區(qū)別.

好吧,我承認(rèn)我是標(biāo)題黨,apply配合表值函數(shù)的用法,只能替代只進(jìn)游標(biāo),但是這種方法效率要比游標(biāo)好的多.

SQLServer 2005 和游標(biāo)cursor說(shuō)再見(jiàn)——apply運(yùn)算符