Использование APPLY
Оператор APPLY позволяет вызывать возвращающую табличное значение функцию для каждой строки, возвращаемой внешним табличным выражением запроса. Возвращающая табличное значение функция выступает в роли правого входа, а внешнее табличное выражение — в роли левого входа. Правый вход оценивается для каждой строки из левого входа, а созданные строки объединяются для конечного вывода. Список столбцов, созданных оператором APPLY, является набором столбцов в левом входе, за которым следует список столбцов, возвращенный правым входом.
.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 D.deptid, D.deptname, D.deptmgrid
,ST.empid, ST.empname, ST.mgrid
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 появился бы в результирующем наборе со значением NULL в поле deptmgrid так же, как и в полях, возвращенных процедурой fn_getsubtree.