Using APPLY
Operator Zastosuj umożliwia wywoływanie funkcja zwracająca tabelę poszczególnych wierszy zwracanych przez wyrażenie tabela zewnętrznej kwerendy.Funkcja wycenione tabela działa jako danych wejściowych po prawej i wyrażenie tabela zewnętrznej działa jako danych wejściowych po lewej.Prawy danych wejściowych jest wyznaczana dla każdego wiersza z lewej wejścia i wierszy, produkowane są łączone na ostatecznym wydruku.Lista kolumn wyprodukowane przez operator Zastosuj jest zestaw kolumn w lewo danych wejściowych następuje lista kolumn zwracana przez prawo dane wejściowe.
Uwaga
Aby użyć Zastosuj, poziom zgodności bazy danych musi być co najmniej 90.
Istnieją dwie formy Zastosuj: Certyfikat KRZYŻOWY Zastosuj i OUTER mają zastosowania. Między DOMENAMI Zastosuj zwraca tylko wiersze z zewnętrznego tabela tego produktu z zestawu wyników funkcja zwracająca tabelę.OUTER mają zastosowania zwraca zarówno wiersze, które wywołują zestaw wyników, jak i wierszy, których nie, o wartości NULL w kolumnach wyprodukowane przez funkcja zwracająca tabelę.
Na przykład należy wziąć pod uwagę następujące tabele Employees i 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);
Większość działów w Departments Tabela ma identyfikator menedżera, który odpowiada pracownika w Employees Tabela. Poniższa funkcja wycenione tabela akceptuje identyfikator pracownika jako argument i zwraca określonego pracownika i wszystkie jego podwładnych.
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
Do zwrócenia wszystkich podwładnych na wszystkich poziomach dla Menedżera poszczególnych działów, za pomocą następującej kwerendy.
SELECT D.deptid, D.deptname, D.deptmgrid
,ST.empid, ST.empname, ST.mgrid
FROM Departments AS D
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST;
Here is the result set.
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
Zwróć uwagę, że każdy wiersz z Departments Tabela zostanie powielony tyle razy, ile wierszy zwrócone przez fn_getsubtree Menedżer jednego z działów firmy.
Ponadto Gardening Dział nie pojawia się w wynikach. Ponieważ żaden menedżer tego działu fn_getsubtree zwracane pustego zestaw dla niego. Za pomocą OUTER APPLY, Gardening Dział pojawią się również zestaw wyników z wartościami null deptmgrid pole także w zwróconej przez pola fn_getsubtree.