SQL Server 2005 新增 cross apply 和 outer apply 聯(lián)接語句
我們知道有個 SQL Server 2000 中有個 cross join 是用于交叉聯(lián)接的。實(shí)際上增加 cross apply 和 outer apply 是用于交叉聯(lián)接表值函數(shù)（返回表結(jié)果集的函數(shù)）的， 更重要的是這個函數(shù)的參數(shù)是另一個表中的字段。這個解釋可能有些含混不請，請看下面的例子：
-- 1. cross join 聯(lián)接兩個表
select *
from TABLE_1 as T1
cross join TABLE_2 as T2
-- 2. cross join 聯(lián)接表和表值函數(shù)，表值函數(shù)的參數(shù)是個“常量”
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.
最后的這個查詢的語法有錯誤。在 cross join 時，表值函數(shù)的參數(shù)不能是表 T1 的字段， 為啥不能這樣做呢？我猜可能微軟當(dāng)時沒有加這個功能：），后來有客戶抱怨后， 于是微軟就增加了 cross apply 和 outer apply 來完善，請看 cross apply, outer apply 的例子：
-- 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 對于 T1 中的每一行都和派生表（表值函數(shù)根據(jù)T1當(dāng)前行數(shù)據(jù)生成的動態(tài)結(jié)果集） 做了一個交叉聯(lián)接。cross apply 和 outer apply 的區(qū)別在于： 如果根據(jù) T1 的某行數(shù)據(jù)生成的派生表為空，cross apply 后的結(jié)果集 就不包含 T1 中的這行數(shù)據(jù)，而 outer apply 仍會包含這行數(shù)據(jù)，并且派生表的所有字段值都為 NULL。
下面的例子摘自微軟 SQL Server 2005 聯(lián)機(jī)幫助，它很清楚的展現(xiàn)了 cross apply 和 outer apply 的不同之處：
-- cross apply
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)
-- outer apply
select *
from Departments as D
outer 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
6 Gardening NULL NULL NULL NULL NULL
(13 row(s) affected)
注意 outer apply 結(jié)果集中多出的最后一行。 當(dāng) Departments 的最后一行在進(jìn)行交叉聯(lián)接時：deptmgrid 為 NULL，fn_getsubtree(D.deptmgrid) 生成的派生表中沒有數(shù)據(jù)，但 outer apply 仍會包含這一行數(shù)據(jù)，這就是它和 cross join 的不同之處。
下面是完整的測試代碼，你可以在 SQL Server 2005 聯(lián)機(jī)幫助上找到：
-- 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
-------------------中國風(fēng)整理的兩個小例子-------------------------------
create table #T(姓名 varchar(10))
insert into #T values('張三')
insert into #T values('李四')
insert into #T values(NULL )
create table #T2(姓名 varchar(10) , 課程 varchar(10) , 分?jǐn)?shù) int)
insert into #T2 values('張三' , '語文' , 74)
insert into #T2 values('張三' , '數(shù)學(xué)' , 83)
insert into #T2 values('張三' , '物理' , 93)
insert into #T2 values(NULL , '數(shù)學(xué)' , 50)
--drop table #t,#T2
go
select
*
from
#T a
cross apply
(select 課程,分?jǐn)?shù) from #t2 where 姓名=a.姓名) b
/*
姓名 課程 分?jǐn)?shù)
---------- ---------- -----------
張三 語文 74
張三 數(shù)學(xué) 83
張三 物理 93
(3 行受影響)
*/
select
*
from
#T a
outer apply
(select 課程,分?jǐn)?shù) from #t2 where 姓名=a.姓名) b
/*
姓名 課程 分?jǐn)?shù)
---------- ---------- -----------
張三 語文 74
張三 數(shù)學(xué) 83
張三 物理 93
李四 NULL NULL
NULL NULL NULL
(5 行受影響)
本文來自CSDN博客，轉(zhuǎn)載請標(biāo)明出處：http://blog.csdn.net/htl258/archive/2009/09/10/4537421.aspx
浙公網(wǎng)安備 33010602011771號