使用 APPLY
更新： 2006 年 4 月 14 日
APPLY 運算子可讓您針對由查詢之外部資料表運算式所傳回的每個資料列，叫用資料表值函數。資料表值函數會作為右方輸入，而外部資料表運算式作為左方輸入。右方輸入是針對來自左方輸入的每個資料列來評估的，而產生的資料列會合併為最終輸出。APPLY 運算子所產生的資料行清單，即為左方輸入中的資料行集合，其後為右方輸入所傳回的資料行清單。
.gif "ms175156.note(zh-tw,SQL.90).gif") 附註： |
|---|
| 若要使用 APPLY，資料庫相容性層級必須是 90。 |
APPLY 有兩種格式：CROSS APPLY 與 OUTER APPLY。CROSS APPLY 所傳回的資料列，僅限於從資料表值函數產生結果集之外部資料表所傳回的資料列。OUTER APPLY 則可傳回能產生結果集的資料列，以及不會產生結果集的資料列 (在資料表值函數所產生的欄位中會顯示 NULL 值)。
舉例來說，請考慮下列資料表 Employees 與 Departments：
--Create Employees table and insert values.
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.
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)
Departments 資料表中的大多數部門都會有一個經理識別碼，對應於 Employees 資料表中的員工。下列資料表值函數會接受員工識別碼作為引數，並傳回該名員工及其所有的部屬。
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
若要針對每個部門的經理傳回其所有層級的每位部屬，請使用下列查詢。
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
請注意，Departments 資料表的每個資料列所重複的次數，會與針對部門經理執行的 fn_getsubtree 所傳回的資料列數一樣多。
另外，Gardening 部門不會出現在結果中。因為此部門沒有經理，所以 fn_getsubtree 會為此部門傳回空的集合。若使用 OUTER APPLY，則 Gardening 部門也會出現在結果集中，但在 deptmgrid 欄位與 fn_getsubtree 傳回的欄位中都會顯示 Null 值。