SQL Server CROSS APPLY和OUTER APPLY的應(yīng)用詳解

更新時(shí)間：2011年10月25日 15:24:47 作者：

SQL Server數(shù)據(jù)庫(kù)操作中，在2005以上的版本新增加了一個(gè)APPLY表運(yùn)算符的功能

SQL Server數(shù)據(jù)庫(kù)操作中，在2005以上的版本新增加了一個(gè)APPLY表運(yùn)算符的功能。新增的APPLY表運(yùn)算符把右表表達(dá)式應(yīng)用到左表表達(dá)式中的每一行。它不像JOIN那樣先計(jì)算那個(gè)表表達(dá)式都可以，APPLY必選先邏輯地計(jì)算左表達(dá)式。這種計(jì)算輸入的邏輯順序允許吧右表達(dá)式關(guān)聯(lián)到左表表達(dá)式。
APPLY有兩種形式，一個(gè)是OUTER APPLY，一個(gè)是CROSS APPLY，區(qū)別在于指定OUTER，意味著結(jié)果集中將包含使右表表達(dá)式為空的左表表達(dá)式中的行，而指定CROSS，則相反，結(jié)果集中不包含使右表表達(dá)式為空的左表表達(dá)式中的行。
理解CROSS APPLY
比如：LargeTable表中的某一列存儲(chǔ)的數(shù)據(jù)是以“:”號(hào)分隔的數(shù)據(jù)，我們處理的時(shí)候，可能要先把這個(gè)值，先分隔，然后把分隔后的每個(gè)值單獨(dú)一行放在一張表中，然后對(duì)這個(gè)表做處理。這只是用其中一行做的處理，如果我們把表中多行都做這樣的處理，把多行以:號(hào)分隔的數(shù)值都放在一個(gè)表中，該怎么處理呢？
用APPLY表運(yùn)算符一行語(yǔ)句就能處理以上操作。

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

 
SELECT 
a 
FROM 
dbo.LargeTable AS LT --實(shí)際表 
CROSS APPLY 
dbo.Split(LT.Name,':') --自定義表值函數(shù)，完成字符串分隔 
WHERE 
a <> '' --去掉結(jié)果表中a字段為空的數(shù)據(jù) 
-----------字符串分隔函數(shù)------------- 
CREATE Function [dbo].[Split] 
( 
@Sql varchar(8000), 
@Splits varchar(10) 
) 
returns @temp Table (a varchar(100)) 
As 
Begin 
Declare @i Int 
Set @Sql = RTrim(LTrim(@Sql)) 
Set @i = CharIndex(@Splits,@Sql) 

While @i >= 1 
Begin 
Insert @temp Values(Left(@Sql,@i-1)) 
Set @Sql = SubString(@Sql,@i+1,Len(@Sql)-@i) 
Set @i = CharIndex(@Splits,@Sql) 
End 

If @Sql <> '' 
Insert @temp Values (@Sql) 

Return 
End 
------------調(diào)用示例------------- 
SELECT 
* 
FROM 
dbo.Split('581::579::519::279::406::361::560',':') 

APPLY的執(zhí)行過程：它先邏輯計(jì)算左表表達(dá)式，然后把右表達(dá)式應(yīng)用到左表表達(dá)式的每一行。實(shí)際是把外部查詢的列引用作為參數(shù)傳遞給表值函數(shù)。
我們知道 SQL Server2000 中有個(gè) cross join 是用于交叉聯(lián)接的。實(shí)際上SQL Server 2005 新增 cross apply 和 outer apply 聯(lián)接語(yǔ)句是用于交叉聯(lián)接表值函數(shù)（返回表結(jié)果集的函數(shù)）的，更重要的是這個(gè)函數(shù)的參數(shù)是另一個(gè)表中的字段。
這個(gè)解釋可能有些含混不請(qǐng)，請(qǐng)看下面的例子：

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

 
-- 1. cross join 聯(lián)接兩個(gè)表 
select * 
　　from TABLE_1 as T1 
cross join TABLE_2 as T2 
-- 2. cross join 聯(lián)接表和表值函數(shù)，表值函數(shù)的參數(shù)是個(gè)“常量” 
select * 
　　from TABLE_1 T1 
cross join FN_TableValue(100) 
-- 3. cross join 聯(lián)接表和表值函數(shù)，表值函數(shù)的參數(shù)是“表T1中的字段” 
select * 
　　from TABLE_1 T1 
cross join FN_TableValue(T1.column_a) 
Msg 4104, Level 16, State 1, Line 1 
The multi-part identifier "T1.column_a" could not be bound. 

最后的這個(gè)查詢的語(yǔ)法有錯(cuò)誤。在cross join 時(shí)，表值函數(shù)的參數(shù)不能是表 T1 的字段，為啥不能這樣做呢？我猜可能微軟當(dāng)時(shí)沒有加這個(gè)功能：），后來(lái)有客戶抱怨后，于是微軟就增加了 cross apply 和 outer apply 來(lái)完善。
請(qǐng)看 cross apply, outer apply 的例子：

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

 
-- 4. cross apply 
select * 
　　from TABLE_1 T1 
cross apply FN_TableValue(T1.column_a) 
-- 5. outer apply 
select * 
　　from TABLE_1 T1 
outer apply FN_TableValue(T1.column_a) 

cross apply 和 outer apply 對(duì)于 T1 中的每一行都和派生表（表值函數(shù)根據(jù)T1當(dāng)前行數(shù)據(jù)生成的動(dòng)態(tài)結(jié)果集）做了一個(gè)交叉聯(lián)接。cross apply 和 outer apply 的區(qū)別在于：如果根據(jù) T1 的某行數(shù)據(jù)生成的派生表為空，cross apply 后的結(jié)果集就不包含 T1 中的這行數(shù)據(jù)，而 outer apply 仍會(huì)包含這行數(shù)據(jù)，并且派生表的所有字段值都為 NULL。
下面的例子摘自微軟 SQL Server 2005 聯(lián)機(jī)幫助，它很清楚的展現(xiàn)了 cross apply 和 outer apply 的不同之處：

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

 
-- cross apply query 
SELECT * 
FROM Departments AS D 
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST 

deptid deptname deptmgrid empid empname mgrid lvl

----------- -----------   ----------- ----------- -----------   ----------- ------
1           HR            2           2           Andrew        1           0
1           HR            2           5           Steven        2           1
1           HR            2           6           Michael       2           1
2           Marketing     7           7           Robert        3           0
2           Marketing     7           11          David         7           1
2           Marketing     7           12          Ron           7           1
2           Marketing     7           13          Dan           7           1
2           Marketing     7           14          James         11          2
3           Finance       8           8           Laura         3           0
4           R&D           9           9           Ann           3           0
5           Training      4           4           Margaret      1           0
5           Training      4           10          Ina           4           1

(12 row(s) affected)

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

 
-- outer apply query 
SELECT * 
FROM Departments AS D 
OUTER APPLY fn_getsubtree(D.deptmgrid) AS ST 

deptid deptname deptmgrid empid empname mgrid lvl

注意outer apply 結(jié)果集中多出的最后一行。當(dāng) Departments 的最后一行在進(jìn)行交叉聯(lián)接時(shí)：deptmgrid 為 NULL，fn_getsubtree(D.deptmgrid) 生成的派生表中沒有數(shù)據(jù)，但 outer apply 仍會(huì)包含這一行數(shù)據(jù)，這就是它和 cross join 的不同之處。
下面是完整的測(cè)試代碼，你可以在 SQL Server 2005 聯(lián)機(jī)幫助上找到：

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

 
-- create Employees table and insert values 
IF OBJECT_ID('Employees') IS NOT NULL 
DROP TABLE Employees 
GO 
CREATE TABLE Employees 
( 
empid INT NOT NULL, 
mgrid INT NULL, 
empname VARCHAR(25) NOT NULL, 
salary MONEY NOT NULL 
) 
GO 
IF OBJECT_ID('Departments') IS NOT NULL 
DROP TABLE Departments 
GO 
-- create Departments table and insert values 
CREATE TABLE Departments 
( 
deptid INT NOT NULL PRIMARY KEY, 
deptname VARCHAR(25) NOT NULL, 
deptmgrid INT 
) 
GO 
-- fill datas 
INSERT INTO employees VALUES (1,NULL,'Nancy',00.00) 
INSERT INTO employees VALUES (2,1,'Andrew',00.00) 
INSERT INTO employees VALUES (3,1,'Janet',00.00) 
INSERT INTO employees VALUES (4,1,'Margaret',00.00) 
INSERT INTO employees VALUES (5,2,'Steven',00.00) 
INSERT INTO employees VALUES (6,2,'Michael',00.00) 
INSERT INTO employees VALUES (7,3,'Robert',00.00) 
INSERT INTO employees VALUES (8,3,'Laura',00.00) 
INSERT INTO employees VALUES (9,3,'Ann',00.00) 
INSERT INTO employees VALUES (10,4,'Ina',00.00) 
INSERT INTO employees VALUES (11,7,'David',00.00) 
INSERT INTO employees VALUES (12,7,'Ron',00.00) 
INSERT INTO employees VALUES (13,7,'Dan',00.00) 
INSERT INTO employees VALUES (14,11,'James',00.00) 
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 departments 
-- table-value function 
IF OBJECT_ID('fn_getsubtree') IS NOT NULL 
DROP FUNCTION fn_getsubtree 
GO 
CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) 
RETURNS TABLE 
AS 
RETURN( 
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 
) 
SELECT * FROM Employees_Subtree 
) 
GO 
-- cross apply query 
SELECT * 
FROM Departments AS D 
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST 
-- outer apply query 
SELECT * 
FROM Departments AS D 
OUTER APPLY fn_getsubtree(D.deptmgrid) AS ST 