SQLSERVER cross/apply使用 

 1 -- create Employees table and insert values
 2 IF OBJECT_ID('Employees') IS NOT NULL
 3 DROP TABLE Employees
 4 GO
 5 CREATE TABLE Employees
 6 (
 7  empid INT NOT NULL,
 8  mgrid INT NULL,
 9  empname VARCHAR(25) NOT NULL,
10  salary MONEY NOT NULL
11 )
12 GO
13 IF OBJECT_ID('Departments') IS NOT NULL
14 DROP TABLE Departments
15 GO
16 -- create Departments table and insert values
17 CREATE TABLE Departments
18 (
19  deptid INT NOT NULL PRIMARY KEY,
20  deptname VARCHAR(25) NOT NULL,
21  deptmgrid INT
22 )
23 GO
24 
25 -- fill datas
26 INSERT INTO employees VALUES  (1,NULL,'Nancy',00.00)
27 INSERT INTO employees VALUES  (2,1,'Andrew',00.00)
28 INSERT INTO employees VALUES  (3,1,'Janet',00.00)
29 INSERT INTO employees VALUES  (4,1,'Margaret',00.00)
30 INSERT INTO employees VALUES  (5,2,'Steven',00.00)
31 INSERT INTO employees VALUES  (6,2,'Michael',00.00)
32 INSERT INTO employees VALUES  (7,3,'Robert',00.00)
33 INSERT INTO employees VALUES  (8,3,'Laura',00.00)
34 INSERT INTO employees VALUES  (9,3,'Ann',00.00)
35 INSERT INTO employees VALUES  (10,4,'Ina',00.00)
36 INSERT INTO employees VALUES  (11,7,'David',00.00)
37 INSERT INTO employees VALUES  (12,7,'Ron',00.00)
38 INSERT INTO employees VALUES  (13,7,'Dan',00.00)
39 INSERT INTO employees VALUES  (14,11,'James',00.00)
40 
41 INSERT INTO departments VALUES  (1,'HR',2)
42 INSERT INTO departments VALUES  (2,'Marketing',7)
43 INSERT INTO departments VALUES  (3,'Finance',8)
44 INSERT INTO departments VALUES  (4,'R&D',9)
45 INSERT INTO departments VALUES  (5,'Training',4)
46 INSERT INTO departments VALUES  (6,'Gardening',NULL)
47 GO
48 --SELECT * FROM departments
49 
50 -- table-value function
51 IF OBJECT_ID('fn_getsubtree') IS NOT NULL
52 DROP FUNCTION  fn_getsubtree
53 GO
54 CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) 
55 RETURNS TABLE 
56 AS 
57 RETURN(
58   WITH Employees_Subtree(empid, empname, mgrid, lvl)
59   AS 
60   (
61     -- Anchor Member (AM)
62     SELECT empid, empname, mgrid, 0
63     FROM employees
64     WHERE empid = @empid   
65     UNION ALL
66     -- Recursive Member (RM)
67     SELECT e.empid, e.empname, e.mgrid, es.lvl+1
68     FROM employees AS e
69        join employees_subtree AS es
70           ON e.mgrid = es.empid
71   )
72     SELECT * FROM Employees_Subtree
73 )
74 GO
75 
76 SELECT * FROM Departments 
77 
78 -- cross apply query
79 SELECT *
80 FROM Departments AS D
81     CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST
82 
83 
84 
85 -- outer apply query
86 SELECT *
87 FROM Departments AS D
88     OUTER APPLY fn_getsubtree(D.deptmgrid) AS ST

 

posted @ 2017-08-29 17:07  haikuang  阅读(558)  评论(0)    收藏  举报