Verwenden von APPLY
Aktualisiert: 14. April 2006
Der APPLY-Operator ermöglicht Ihnen das Aufrufen einer Tabellenwertfunktion für sämtliche Zeilen, die von einem äußeren Ausdruck einer Tabelle einer Abfrage zurückgegeben werden. Die Tabellenwertfunktion dient als rechte Eingabe, der äußere /Ausdruck der Tabelle agiert als linke Eingabe. Die rechte Eingabe wird für jede Zeile aus der linken Eingabe ausgewertet, und die erstellten Zeilen werden für die endgültige Ausgabe kombiniert. Bei der Liste der vom APPLY-Operator erstellten Spalten handelt es sich um die Reihe von Spalten in der linken Eingabe, gefolgt von der Liste der von der rechten Eingabe zurückgegebenen Spalten.
.gif "ms175156.note(de-de,SQL.90).gif") Hinweis: |
|---|
| Zum Verwenden von APPLY muss der Kompatibilitätsgrad der Datenbank auf 90 festgelegt sein. |
Es gibt zwei Formen von APPLY: CROSS APPLY und OUTER APPLY. CROSS APPLY gibt nur Zeilen von der äußeren Tabelle zurück, die ein Resultset der Tabellenwertfunktion erstellen. OUTER APPLY gibt sowohl Zeilen, die ein Resultset erstellen als auch solche Zeilen zurück, bei denen dies nicht der Fall ist. Durch die Tabellenwertfunktion werden hierbei in den Spalten NULL-Werte erstellt.
Betrachten Sie als Beispiel die Tabellen Employees und 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)
Die meisten Abteilungen in der Departments-Tabelle weisen eine Manager-ID auf, die einem Mitarbeiter in der Employees-Tabelle entspricht. Die folgende Tabellenwertfunktion akzeptiert eine Mitarbeiter-ID als Argument und gibt diesen Mitarbeiter sowie alle seine Untergebenen zurück.
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
Verwenden Sie die folgende Abfrage, um sämtliche Untergebene auf sämtlichen Ebenen für die Manager der einzelnen Abteilungen zurückzugeben:
SELECT *
FROM Departments AS D
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST
Dies ist das Resultset.
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
Beachten Sie, dass jede Zeile aus der Departments-Tabelle so oft dupliziert wird, wie Zeilen von fn_getsubtree für den Manager der Abteilung zurückgegeben werden.
Außerdem wird die Gardening-Abteilung in den Ergebnissen nicht angezeigt. Da diese Abteilung keinen Manager hat, hat fn_getsubtree hierfür einen leeren Satz zurückgegeben. Wenn OUTER APPLY verwendet wird, wird die Gardening-Abteilung ebenfalls im Resultset angezeigt. In diesem Fall enthalten das deptmgrid-Feld sowie die von fn_getsubtree zurückgegebenen Felder NULL-Werte.