Condividi tramite


Utilizzo di APPLY

L'operatore APPLY consente di richiamare una funzione con valori di tabella per ogni riga restituita da un'espressione di tabella esterna di una query. La funzione con valori di tabella opera come input destro, mentre l'espressione di tabella esterna opera come input sinistro. L'input destro viene valutato per ogni riga dell'input sinistro e le righe prodotte vengono combinate per l'output finale. L'elenco di colonne prodotto dall'operatore APPLY corrisponde al set di colonne nell'input sinistro seguito dall'elenco di colonne restituito dall'input destro.

Nota

Per l'utilizzo di APPLY il livello di compatibilità del database deve essere almeno 90.

L'operatore APPLY è disponibile in due forme, ovvero CROSS APPLY e OUTER APPLY. L'operatore CROSS APPLY restituisce solo le righe della tabella esterna che producono un set di risultati dalla funzione con valori di tabella. L'operatore OUTER APPLY restituisce le righe che producono un set di risultati e quelle che non producono alcun set di risultati, con valori NULL nelle colonne prodotte dalla funzione con valori di tabella.

Si considerino, come esempio, le tabelle Employees e Departments seguenti:

--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);

Per la maggior parte dei reparti inclusi nella tabella Departments è disponibile un ID responsabile che corrisponde a un dipendente incluso nella tabella Employees. La funzione con valori di tabella seguente accetta un ID dipendente come argomento e restituisce il dipendente e tutti i relativi subalterni.

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

Per restituire tutti i subalterni in tutti i livelli per il responsabile di ogni reparto, utilizzare la query seguente.

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;

Set di risultati:

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

Si noti che ogni riga della tabella Departments è duplicata tante volte quante sono le righe restituite da fn_getsubtree per il responsabile del reparto.

Il reparto Gardening, inoltre, non è incluso nei risultati. Poiché per questo reparto non esiste un responsabile, fn_getsubtree ha restituito un set vuoto. Se si utilizza l'operatore OUTER APPLY, nel set di risultati verrà visualizzato anche il reparto Gardening, con valori NULL nel campo deptmgrid, nonché nei campi restituiti da fn_getsubtree.

Vedere anche

Riferimento