Cross Apply 和 Outer Apply

-- create Employees table and insert valuesIF OBJECT_ID('Employees') IS NOT NULLDROP TABLE EmployeesGOCREATE TABLE Employees(empid INT NOT NULL,mgrid INT NULL,empname VARCHAR(25) NOT NULL,salary MONEY NOT NULL)GOIF OBJECT_ID('Departments') IS NOT NULLDROP TABLE DepartmentsGO-- create Departments table and insert valuesCREATE TABLE Departments(deptid INT NOT NULL PRIMARY KEY,deptname VARCHAR(25) NOT NULL,deptmgrid INT)GO-- fill datasINSERT INTO employees VALUES  (1,NULL,'Nancy',00.00)INSERT INTO employees VALUES  (2,1,'Andrew',00.00)INSERT INTO employees VALUES  (3,1,'Janet',00.00)INSERT INTO employees VALUES  (4,1,'Margaret',00.00)INSERT INTO employees VALUES  (5,2,'Steven',00.00)INSERT INTO employees VALUES  (6,2,'Michael',00.00)INSERT INTO employees VALUES  (7,3,'Robert',00.00)INSERT INTO employees VALUES  (8,3,'Laura',00.00)INSERT INTO employees VALUES  (9,3,'Ann',00.00)INSERT INTO employees VALUES  (10,4,'Ina',00.00)INSERT INTO employees VALUES  (11,7,'David',00.00)INSERT INTO employees VALUES  (12,7,'Ron',00.00)INSERT INTO employees VALUES  (13,7,'Dan',00.00)INSERT INTO employees VALUES  (14,11,'James',00.00)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)GOselect * from employees/*      empid       mgrid empname                                  salary----------- ----------- ------------------------- ---------------------          1        NULL Nancy                                      0.00          2           1 Andrew                                     0.00          3           1 Janet                                      0.00          4           1 Margaret                                   0.00          5           2 Steven                                     0.00          6           2 Michael                                    0.00          7           3 Robert                                     0.00          8           3 Laura                                      0.00          9           3 Ann                                        0.00         10           4 Ina                                        0.00         11           7 David                                      0.00         12           7 Ron                                        0.00         13           7 Dan                                        0.00         14          11 James                                      0.00*/SELECT * FROM departments/*     deptid deptname                    deptmgrid----------- ------------------------- -----------          1 HR                                  2          2 Marketing                           7          3 Finance                             8          4 R&D                                 9          5 Training                            4          6 Gardening                        NULL*/-- table-value functionIF OBJECT_ID('fn_getsubtree') IS NOT NULLDROP FUNCTION  fn_getsubtreeGOCREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) RETURNS TABLE AS RETURN(  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  )    SELECT * FROM Employees_Subtree)GO--在部门经理为id为2 的下面，有如下三个成员SELECT * FROM dbo.fn_getsubtree( 2)/*      empid empname                         mgrid         lvl----------- ------------------------- ----------- -----------          2 Andrew                              1           0          5 Steven                              2           1          6 Michael                             2           1 */--列出所有部门的部门经理及部门成员(没有的不列出)-- cross apply querySELECT *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(12 行受影响)*/--列出所有部门的部门经理及部门成员(没有的也必须列出)-- outer apply querySELECT *FROM Departments AS D    OUTER 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          6 Gardening                        NULL        NULL NULL                             NULL        NULL (13 行受影响) */