SQL 中的 图 树 层次结构[转自http://blog.csdn.net/feixianxxx/article/details/4753783]

在RDBMS中操作 图 树 层次结构 等特殊的数据结构时,我们通常采用2个主要方法:
1.基于迭代/递归

2.具体化描述数据结构的附加信息。
一般模型有：员工组织图(树,层次结构);料表--BOM(有向图);道路系统(无向循环图)

1.迭代/递归
迭代可以迭代图的一个节点,也可以迭代一个层次.后者比前者要快很多.
实现方法：SQL2000通过UDF(用户自定义函数),SQL2005使用CTE。

 

a.下属问题(通俗说,求子节点)
--这里我使用书上的员工表(表内容如下)

[c-sharp]  view plain copy print ?

1.  SET NOCOUNT ON;  
2. USE tempdb;  
3. GO  
4. IF OBJECT_ID('dbo.Employees') IS NOT NULL  
5.   DROP TABLE dbo.Employees;  
6. GO  
7. CREATE TABLE dbo.Employees  
8. (  
9.   empid   INT         NOT NULL PRIMARY KEY,  
10.   mgrid   INT         NULL     REFERENCES dbo.Employees,  
11.   empname VARCHAR(25) NOT NULL,  
12.   salary  MONEY       NOT NULL,  
13.   CHECK (empid <> mgrid)  
14. );  
15. INSERT INTO dbo.Employees(empid, mgrid, empname, salary)  
16.   VALUES(1, NULL, 'David', $10000.00);  
17. INSERT INTO dbo.Employees(empid, mgrid, empname, salary)  
18.   VALUES(2, 1, 'Eitan', $7000.00);  
19. INSERT INTO dbo.Employees(empid, mgrid, empname, salary)  
20.   VALUES(3, 1, 'Ina', $7500.00);  
21. INSERT INTO dbo.Employees(empid, mgrid, empname, salary)  
22.   VALUES(4, 2, 'Seraph', $5000.00);  
23. INSERT INTO dbo.Employees(empid, mgrid, empname, salary)  
24.   VALUES(5, 2, 'Jiru', $5500.00);  
25. INSERT INTO dbo.Employees(empid, mgrid, empname, salary)  
26.   VALUES(6, 2, 'Steve', $4500.00);  
27. INSERT INTO dbo.Employees(empid, mgrid, empname, salary)  
28.   VALUES(7, 3, 'Aaron', $5000.00);  
29. INSERT INTO dbo.Employees(empid, mgrid, empname, salary)  
30.   VALUES(8, 5, 'Lilach', $3500.00);  
31. INSERT INTO dbo.Employees(empid, mgrid, empname, salary)  
32.   VALUES(9, 7, 'Rita', $3000.00);  
33. INSERT INTO dbo.Employees(empid, mgrid, empname, salary)  
34.   VALUES(10, 5, 'Sean', $3000.00);  
35. INSERT INTO dbo.Employees(empid, mgrid, empname, salary)  
36.   VALUES(11, 7, 'Gabriel', $3000.00);  
37. INSERT INTO dbo.Employees(empid, mgrid, empname, salary)  
38.   VALUES(12, 9, 'Emilia' , $2000.00);  
39. INSERT INTO dbo.Employees(empid, mgrid, empname, salary)  
40.   VALUES(13, 9, 'Michael', $2000.00);  
41. INSERT INTO dbo.Employees(empid, mgrid, empname, salary)  
42.   VALUES(14, 9, 'Didi', $1500.00);  
43. --创建索引  
44. CREATE UNIQUE INDEX idx_unc_mgrid_empid ON dbo.Employees(mgrid, empid);  
45. go  

--SQL2000 udf方法：

 

[css] view plaincopyprint?

1. IF OBJECT_ID('dbo.fn_subordinates1') IS NOT NULL  
2.   DROP FUNCTION dbo.fn_subordinates1;  
3. GO  
4. CREATE FUNCTION dbo.fn_subordinates1(@root AS INT)   
5. RETURNS @Subs TABLE  
6. (  
7.   empid INT NOT NULL PRIMARY KEY NONCLUSTERED,  
8.   lvl   INT NOT NULL,  
9.   UNIQUE CLUSTERED(lvl, empid)    
10. )  
11. AS  
12. begin   
13. declare @lv int   
14. set @lv=0  
15. insert @Subs values(@root,@lv)  
16. while @@rowcount>0  
17. begin   
18.  set @lv=@Lv+1;  
19.  insert @subs  
20.  select b.empid ,@Lv  
21.  from @subs a join dbo.Employees b on a.empid=b.mgrid and lvl=@lv-1  
22. end  
23. return;  
24. end   
25. go  
26. SELECT empid, lvl FROM dbo.fn_subordinates1(3) AS S;  
27.   
28.    

 

--SQL2005 CTE

 

[c-sharp] view plaincopyprint?

1. DECLARE @root AS INT;  
2. SET @root = 3;  
3. WITH SubsCTE  
4. AS  
5. (  
6.   -- Anchor member returns root node  
7.   SELECT empid, empname, 0 AS lvl  
8.   FROM dbo.Employees  
9.   WHERE empid = @root  
10.   
11.   UNION ALL  
12.   
13.   -- Recursive member returns next level of children  
14.   SELECT C.empid, C.empname, P.lvl + 1  
15.   FROM SubsCTE AS P  
16.     JOIN dbo.Employees AS C  
17.       ON C.mgrid = P.empid  
18. )  
19. SELECT * FROM SubsCTE;  

 

 

--查询结果

[c-sharp]  view plain copy print ?

1. /* 
2. empid       empname                   lvl 
3. ----------- ------------------------- ----------- 
4. 3           Ina                       0 
5. 7           Aaron                     1 
6. 9           Rita                      2 
7. 11          Gabriel                   2 
8. 12          Emilia                    3 
9. 13          Michael                   3 
10. 14          Didi                      3 
11.  
12. */  

---------------如果需要限制递归的层数------------

--SQL2000

 

[c-sharp]  view plain copy print ?

1. IF OBJECT_ID('dbo.fn_subordinates2') IS NOT NULL  
2.   DROP FUNCTION dbo.fn_subordinates2;  
3. GO  
4. CREATE FUNCTION dbo.fn_subordinates2  
5.   (@root AS INT, @maxlevels AS INT = NULL) RETURNS @Subs TABLE  
6. (  
7.   empid INT NOT NULL PRIMARY KEY NONCLUSTERED,  
8.   lvl   INT NOT NULL,  
9.   UNIQUE CLUSTERED(lvl, empid)    
10. )  
11. AS  
12. BEGIN  
13.   DECLARE @lvl AS INT;  
14.   SET @lvl = 0;              
15.   -- 如果@maxlevels输入的是NULL,把他变为最大的INT类型  
16.  SET @maxlevels = COALESCE(@maxlevels, 2147483647);  
17.   INSERT INTO @Subs(empid, lvl)  
18.     SELECT empid, @lvl FROM dbo.Employees WHERE empid = @root;  
19.   
20.   WHILE @@rowcount > 0           
21.     AND @lvl < @maxlevels       -- 这里注意加一个小于最大的递归数的条件,是小于 不是小于等于   
22.   BEGIN  
23.     SET @lvl = @lvl + 1;          
24.   
25.     INSERT INTO @Subs(empid, lvl)  
26.       SELECT C.empid, @lvl               --这里跟上面处理都一样  
27.       FROM @Subs AS P             
28.         JOIN dbo.Employees AS C   
29.           ON P.lvl = @lvl - 1    
30.           AND C.mgrid = P.empid;  
31.   END  
32.   
33.   RETURN;  
34. END  
35. GO  
36. SELECT empid, lvl  
37. FROM dbo.fn_subordinates2(3, NULL) AS S;  

 

--查询结果

[c-sharp]  view plain copy print ?

1. /* 
2. empid       lvl 
3. ----------- ----------- 
4. 3           0 
5. 7           1 
6. 9           2 
7. 11          2 
8. 12          3 
9. 13          3 
10. 14          3 
11. */  
12. -----  

SELECT empid, lvl
FROM dbo.fn_subordinates2(2, NULL) AS S;

[c-sharp]  view plain copy print ?

1. /* 
2. empid       lvl 
3. ----------- ----------- 
4. 2           0 
5. 4           1 
6. 5           1 
7. 6           1 
8. 8           2 
9. 10          2 
10. */  

PS：这里控制返回的层数 你当然可以用最开始的方法,然后再筛选语句里控制层数 
 如SELECT empid, lvl FROM dbo.fn_subordinates1(3) AS S where lvl<3;  但是控制本身的递归只能在UDF里面了
 
 --sql2005方法类似

[c-sharp]  view plain copy print ?

1.  WITH SubsCTE  
2. AS  
3. (  
4.   SELECT empid, empname, 0 AS lvl  
5.   FROM dbo.Employees  
6.   WHERE empid = @root  
7.   UNION ALL  
8.   
9.   SELECT C.empid, C.empname, P.lvl + 1  
10.   FROM SubsCTE AS P  
11.     JOIN dbo.Employees AS C  
12.       ON C.mgrid = P.empid  
13.       AND P.lvl < @maxlevels -- 这里控制递归数  
14. )  
15. SELECT * FROM SubsCTE;  

 

--还有一种偏方：但是不推荐 虽然结果正确 但是会报错

[c-sharp]  view plain copy print ?

1. WITH SubsCTE  
2. AS  
3. (  
4.   SELECT empid, empname, 0 AS lvl  
5.   FROM dbo.Employees  
6.   WHERE empid = @root  
7.   
8.   UNION ALL  
9.   
10.   SELECT C.empid, C.empname, P.lvl + 1  
11.   FROM SubsCTE AS P  
12.     JOIN dbo.Employees AS C  
13.       ON C.mgrid = P.empid  
14. )  
15. SELECT * FROM SubsCTE  
16. OPTION (MAXRECURSION 2);--就是这里的MAXRECURSION 控制递归  

 

--查询结果

[c-sharp]  view plain copy print ?

1.  /* 
2.  empid       empname                   lvl 
3. ----------- ------------------------- ----------- 
4. 3           Ina                       0 
5. 7           Aaron                     1 
6. 9           Rita                      2 
7. 11          Gabriel                   2 
8. 消息 530，级别 16，状态 1，第 4 行 
9. 语句被终止。完成执行语句前已用完最大递归 2。 
10.  */  

 
 

b.祖先(通俗说：求父节点)
其实思路跟子节点差不多

--SQL2000

[c-sharp]  view plain copy print ?

1. IF OBJECT_ID('dbo.fn_managers') IS NOT NULL  
2.   DROP FUNCTION dbo.fn_managers;  
3. GO  
4. CREATE FUNCTION dbo.fn_managers  
5. (@empid AS INT, @maxlevels AS INT = NULL) RETURNS @Mgrs TABLE  
6. (  
7.   empid INT NOT NULL PRIMARY KEY,  
8.   lvl   INT NOT NULL  
9. )  
10. AS  
11. BEGIN  
12.   IF NOT EXISTS(SELECT * FROM dbo.Employees WHERE empid = @empid) --这里判断是否存在经理,不存在 马上返回  
13.     RETURN;  
14.   
15.   DECLARE @lvl AS INT,@mgrid int ;  
16.   SET @lvl = 0;    
17.   set @mgrid=@empid  --赋值给@mgrid 我这是为了更加清楚变量的意思 其实不用这个@Mgrid变量的            
18.   
19.   -- 如果@maxlevels输入的是NULL,把他变为最大的INT类型  
20.   SET @maxlevels = COALESCE(@maxlevels, 2147483647);  
21.   
22.   WHILE  @lvl <= @maxlevels and @mgrid is not null     --注意这里是小于等于 而且新插入的经理不可以为NULL，为null说明是老大了  
23.   BEGIN  
24.  --插入当前经理  
25.  INSERT INTO @Mgrs(empid, lvl) VALUES(@mgrid, @lvl); --这里第一次插入是自己,后面就是各自的经理了  
26.     SET @lvl = @lvl + 1;          
27.     --获得下一个级别的经理  
28.     SET @mgrid = (SELECT mgrid FROM dbo.Employees  
29.                   WHERE empid = @mgrid);  
30.     
31.   END  
32.   
33.   RETURN;  
34. END  
35. GO  
36. SELECT empid, lvl  
37. FROM dbo.fn_managers(8, NULL) AS M;  

--查询结果

[c-sharp]  view plain copy print ?

1. /* 
2. empid       lvl 
3. ----------- ----------- 
4. 1           3 
5. 2           2 
6. 5           1 
7. 8           0 
8.  
9. */  

SELECT empid, lvl
FROM dbo.fn_managers(8, 2) AS M;

[c-sharp]  view plain copy print ?

1. /* 
2. empid       lvl 
3. ----------- ----------- 
4. 2           2 
5. 5           1 
6. 8           0 
7. */  

 

 

--sql2005 

[c-sharp]  view plain copy print ?

1. DECLARE @empid AS INT, @maxlevels AS INT;  
2. SET @empid = 8;  
3. SET @maxlevels = 2;  
4.   
5. WITH MgrsCTE  
6. AS  
7. (  
8.   SELECT empid, mgrid, empname, 0 AS lvl  
9.   FROM dbo.Employees  
10.   WHERE empid = @empid  
11.   
12.   UNION ALL  
13.   
14.   SELECT P.empid, P.mgrid, P.empname, C.lvl + 1  
15.   FROM MgrsCTE AS C  
16.     JOIN dbo.Employees AS P  
17.       ON C.mgrid = P.empid  
18.       AND C.lvl < @maxlevels--限制递归数...  
19. )  
20. SELECT * FROM MgrsCTE;  

 

C.层次显示

 

--SQL2000,思路跟下属问题一模一样 只是多了个PATH

[c-sharp]  view plain copy print ?

1. IF OBJECT_ID('dbo.fn_subordinates3') IS NOT NULL  
2.   DROP FUNCTION dbo.fn_subordinates3;  
3. GO  
4. CREATE FUNCTION dbo.fn_subordinates3  
5.   (@root AS INT, @maxlevels AS INT = NULL) RETURNS @Subs TABLE  
6. (  
7.   empid INT          NOT NULL PRIMARY KEY NONCLUSTERED,  
8.   lvl   INT          NOT NULL,  
9.   path  VARCHAR(900) NOT NULL  
10.   UNIQUE CLUSTERED(lvl, empid)   
11. )  
12. AS  
13. BEGIN  
14.   DECLARE @lvl AS INT;  
15.   SET @lvl = 0;                  
16.   
17.   SET @maxlevels = COALESCE(@maxlevels, 2147483647);  
18.   
19.   
20.   INSERT INTO @Subs(empid, lvl, path)  
21.     SELECT empid, @lvl, CAST(empid AS VARCHAR(100))--ZHU YI ZHE LI   
22.     FROM dbo.Employees   
23.     WHERE empid = @root;  
24.   
25.   WHILE @@rowcount > 0            
26.     AND @lvl < @maxlevels         
27.   BEGIN  
28.     SET @lvl = @lvl + 1;         
29.   
30.   
31.     INSERT INTO @Subs(empid, lvl, path)  
32.       SELECT C.empid, @lvl,  
33.         P.path +'-'+ CAST(C.empid AS VARCHAR(100)) --和上面类型保持一致  
34.       FROM @Subs AS P             
35.         JOIN dbo.Employees AS C   
36.           ON P.lvl = @lvl - 1    
37.           AND C.mgrid = P.empid;  
38.   END  
39.   
40.   RETURN;  
41. END  
42. GO  

--这里的显示分2种 
--显示1
select empid ,pos=REPLICATE('-',lvl)+rtrim(empid)
FROM dbo.fn_subordinates3(1, NULL) AS S 
ORDER BY PATH 

[c-sharp]  view plain copy print ?

1. /* 
2. empid       pos 
3. ----------- ------------ 
4. 1           1 
5. 2           -2 
6. 4           --4 
7. 5           --5 
8. 10          ---10 
9. 8           ---8 
10. 6           --6 
11. 3           -3 
12. 7           --7 
13. 11          ---11 
14. 9           ---9 
15. 12          ----12 
16. 13          ----13 
17. 14          ----14 
18. */  

--还有一种
SELECT empid,  path
FROM dbo.fn_subordinates3(1, NULL) AS S
ORDER BY PATH 

[c-sharp]  view plain copy print ?

1. /* 
2. empid       path 
3. ----------- ---------------- 
4. 1           1 
5. 2           1-2 
6. 4           1-2-4 
7. 5           1-2-5 
8. 10          1-2-5-10 
9. 8           1-2-5-8 
10. 6           1-2-6 
11. 3           1-3 
12. 7           1-3-7 
13. 11          1-3-7-11 
14. 9           1-3-7-9 
15. 12          1-3-7-9-12 
16. 13          1-3-7-9-13 
17. 14          1-3-7-9-14 
18. */  

--当然上面的显示方式还很多,自己可以控制 比如不加ORDER 就可以排出不一样的

 

--SQL2005

[c-sharp]  view plain copy print ?

1. DECLARE @root AS INT;  
2. SET @root = 1;  
3.   
4. WITH SubsCTE  
5. AS  
6. (  
7.   SELECT empid, empname, 0 AS lvl,  
8.     -- Path of root = '.' + empid + '.'  
9.     CAST(CAST(empid AS VARCHAR(10))   
10.          AS VARCHAR(MAX)) AS path  
11.   FROM dbo.Employees  
12.   WHERE empid = @root  
13.   
14.   UNION ALL  
15.   
16.   SELECT C.empid, C.empname, P.lvl + 1,  
17.   
18.     CAST(P.path+'-' + CAST(C.empid AS VARCHAR(10))   
19.          AS VARCHAR(MAX)) AS path  
20.   FROM SubsCTE AS P  
21.     JOIN dbo.Employees AS C  
22.       ON C.mgrid = P.empid  
23. )  
24. SELECT empid, REPLICATE(' | ', lvl) + empname AS empname  
25. FROM SubsCTE  
26. ORDER BY path  

;

 

--结果查询

[c-sharp]  view plain copy print ?

1. /* 
2. empid       empname 
3. ----------- ------------------------- 
4. 1           David 
5. 2            | Eitan 
6. 4            |  | Seraph 
7. 5            |  | Jiru 
8. 10           |  |  | Sean 
9. 8            |  |  | Lilach 
10. 6            |  | Steve 
11. 3            | Ina 
12. 7            |  | Aaron 
13. 11           |  |  | Gabriel 
14. 9            |  |  | Rita 
15. 12           |  |  |  | Emilia 
16. 13           |  |  |  | Michael 
17. 14           |  |  |  | Didi 
18.  
19. */   
20.   
21.                
22.   
23.    

 

 

D.检测循环中异常
说白了 就是检查表里有没出现1-2-4-1这种头接尾的圈.这在现实中是不可能的.一个经理部可能是它手下的手下.-- ||
我们对表做手脚,把老大的经理改成是它的一个员工

[c-sharp]  view plain copy print ?

1. UPDATE dbo.Employees SET mgrid = 14 WHERE empid = 1;  
2. DECLARE @root AS INT;  
3. SET @root = 1;  
4.   
5. WITH SubsCTE  
6. AS  
7. (  
8.   SELECT empid, empname, 0 AS lvl,  
9.     CAST( CAST(empid AS VARCHAR(10))  
10.          AS VARCHAR(MAX)) AS path,  
11.     -- 第一层是不会出现圈圈的  
12.     0 AS cycle --0表示没有圈 1表示出现圈  
13.   FROM dbo.Employees  
14.   WHERE empid = @root  
15.   
16.   UNION ALL  
17.   
18.   SELECT C.empid, C.empname, P.lvl + 1,  
19.     CAST(P.path + '-'+CAST(C.empid AS VARCHAR(10))   
20.          AS VARCHAR(MAX)) AS path,  
21.     -- 这里需要检查  
22.     CASE WHEN P.path LIKE '%' + CAST(C.empid AS VARCHAR(10)) + '%'  
23.       THEN 1 ELSE 0 END  
24.   FROM SubsCTE AS P  
25.     JOIN dbo.Employees AS C  
26.       ON C.mgrid = P.empid  
27.    where p.cycle=0 --保证不会继续在错误的地方继续循环下去  
28. )  
29. SELECT empid, empname, cycle, path  
30. FROM SubsCTE  
31. where cycle=1  

 

--查询结果

[c-sharp]  view plain copy print ?

1. /* 
2. empid       empname                   cycle       path 
3. ----------- ------------------------- ----------- ------------------ 
4. 1           David                     1           1-3-7-9-14-1 
5.  
6. */  

--这样管理员可以轻易找到那个错误的地方.
改过来：UPDATE dbo.Employees SET mgrid = NULL WHERE empid = 1;

 

 

2.具体化路径
这里就是新增加2列，一列是级别 一列是节点路径，这样可以避免每次都去计算.
2个优点：不需要递归,只需要基于集合 ；查询可以使用到路径索引

 

a.维护数据

1.添加不管理员工的员工

[c-sharp]  view plain copy print ?

1. SET NOCOUNT ON;  
2. USE tempdb;  
3. GO  
4. IF OBJECT_ID('dbo.Employees') IS NOT NULL  
5.   DROP TABLE dbo.Employees;  
6. GO  
7. CREATE TABLE dbo.Employees  
8. (  
9.   empid   INT          NOT NULL PRIMARY KEY NONCLUSTERED,  
10.   mgrid   INT          NULL     REFERENCES dbo.Employees,  
11.   empname VARCHAR(25)  NOT NULL,  
12.   salary  MONEY        NOT NULL,  
13.   lvl     INT          NOT NULL,  
14.   path    VARCHAR(900) NOT NULL UNIQUE CLUSTERED  
15. );  
16. CREATE UNIQUE INDEX idx_unc_mgrid_empid ON dbo.Employees(mgrid, empid);  
17. GO  
18. IF OBJECT_ID('dbo.usp_insertemp') IS NOT NULL  
19.   DROP PROC dbo.usp_insertemp;  
20. GO  
21. CREATE PROC dbo.usp_insertemp  
22.   @empid   INT,  
23.   @mgrid   INT,  
24.   @empname VARCHAR(25),  
25.   @salary  MONEY  
26. AS  
27.   
28. SET NOCOUNT ON;  
29.   
30. -- 如果插入的是一个没有经理的老大  
31. IF @mgrid IS NULL  
32.   INSERT INTO dbo.Employees(empid, mgrid, empname, salary, lvl, path)  
33.     VALUES(@empid, @mgrid, @empname, @salary,  
34.       0,  CAST(@empid AS VARCHAR(10)));  
35. --插入有经理的小弟  
36. ELSE  
37.   INSERT INTO dbo.Employees(empid, mgrid, empname, salary, lvl, path)  
38.     SELECT @empid, @mgrid, @empname, @salary,  
39.       lvl + 1, path +'-'+ CAST(@empid AS VARCHAR(10))   
40.     FROM dbo.Employees  
41.     WHERE empid = @mgrid;  
42. GO  
43.   
44. EXEC dbo.usp_insertemp  
45.   @empid = 1, @mgrid = NULL, @empname = 'David', @salary = $10000.00;  
46. EXEC dbo.usp_insertemp  
47.   @empid = 2, @mgrid = 1, @empname = 'Eitan', @salary = $7000.00;  
48. EXEC dbo.usp_insertemp  
49.   @empid = 3, @mgrid = 1, @empname = 'Ina', @salary = $7500.00;  
50. EXEC dbo.usp_insertemp  
51.   @empid = 4, @mgrid = 2, @empname = 'Seraph', @salary = $5000.00;  
52. EXEC dbo.usp_insertemp  
53.   @empid = 5, @mgrid = 2, @empname = 'Jiru', @salary = $5500.00;  
54. EXEC dbo.usp_insertemp  
55.   @empid = 6, @mgrid = 2, @empname = 'Steve', @salary = $4500.00;  
56. EXEC dbo.usp_insertemp  
57.   @empid = 7, @mgrid = 3, @empname = 'Aaron', @salary = $5000.00;  
58. EXEC dbo.usp_insertemp  
59.   @empid = 8, @mgrid = 5, @empname = 'Lilach', @salary = $3500.00;  
60. EXEC dbo.usp_insertemp  
61.   @empid = 9, @mgrid = 7, @empname = 'Rita', @salary = $3000.00;  
62. EXEC dbo.usp_insertemp  
63.   @empid = 10, @mgrid = 5, @empname = 'Sean', @salary = $3000.00;  
64. EXEC dbo.usp_insertemp  
65.   @empid = 11, @mgrid = 7, @empname = 'Gabriel', @salary = $3000.00;  
66. EXEC dbo.usp_insertemp  
67.   @empid = 12, @mgrid = 9, @empname = 'Emilia', @salary = $2000.00;  
68. EXEC dbo.usp_insertemp  
69.   @empid = 13, @mgrid = 9, @empname = 'Michael', @salary = $2000.00;  
70. EXEC dbo.usp_insertemp  
71.   @empid = 14, @mgrid = 9, @empname = 'Didi', @salary = $1500.00  

;
----检测
SELECT empid, mgrid, empname, salary, lvl, path
FROM dbo.Employees
ORDER BY path;

[c-sharp]  view plain copy print ?

1. /* 
2. empid       mgrid       empname                   salary                lvl         path 
3. ----------- ----------- ------------------------- --------------------- ----------- --------- 
4. 1           NULL        David                     10000.00              0           1 
5. 2           1           Eitan                     7000.00               1           1-2 
6. 4           2           Seraph                    5000.00               2           1-2-4 
7. 5           2           Jiru                      5500.00               2           1-2-5 
8. 10          5           Sean                      3000.00               3           1-2-5-10 
9. 8           5           Lilach                    3500.00               3           1-2-5-8 
10. 6           2           Steve                     4500.00               2           1-2-6 
11. 3           1           Ina                       7500.00               1           1-3 
12. 7           3           Aaron                     5000.00               2           1-3-7 
13. 11          7           Gabriel                   3000.00               3           1-3-7-11 
14. 9           7           Rita                      3000.00               3           1-3-7-9 
15. 12          9           Emilia                    2000.00               4           1-3-7-9-12 
16. 13          9           Michael                   2000.00               4           1-3-7-9-13 
17. 14          9           Didi                      1500.00               4           1-3-7-9-14 
18.  
19. */  
20.   
21.    

 

 

2.移动子树 
比如说某个部门来了个新老大,原来部门老大和手下的人都要跟着他.这个时候表里的路径和级别都要更新
Select Empid, Replicate(' | ', Lvl) + Empname As Empname, Lvl, Path
From Dbo.Employees
Order By Path;

--这个是移动之前的层次分布

[c-sharp]  view plain copy print ?

1. /* 
2. empid       empname             lvl             path  
3.  
4. ----------- ----------------- ----------- ------------ 
5. 1           David                   0           1                      
6. 2            | Eitan                1           1-2 
7. 4            |  | Seraph            2           1-2-4                      
8. 5            |  | Jiru              2           1-2-5            
9. 10           |  | | Sean            3           1-2-5-10       
10. 6            |  | Steve             2           1-2-6                 
11. 3            | Ina                  1           1-3              
12. 7            |  | Aaron             2           1-3-7                    
13. 11           |  |  | Gabriel        3           1-3-7-11          
14. 9            |  |  | Rita           3           1-3-7-9           
15. 13           |  |  |  | Michael     4           1-3-7-9-13      
16. 14           |  |  |  | Didi        4           1-3-7-9-14        
17. */  

 

==接下来我们移动

[c-sharp]  view plain copy print ?

1. IF OBJECT_ID('dbo.usp_movesubtree') IS NOT NULL  
2.   DROP PROC dbo.usp_movesubtree;  
3. GO  
4. CREATE PROC dbo.usp_movesubtree  
5.   @root  INT,--旧经理  
6.   @mgrid INT--新经理  
7. AS  
8.   
9. SET NOCOUNT ON;  
10.   
11. BEGIN TRAN;  
12.   
13.   UPDATE E  
14.     SET lvl  = E.lvl + NM.lvl - OM.lvl,-- 级别=当前的级别+(新经理级别-原经理级别)  
15.         path = STUFF(E.path, 1, LEN(OM.path), NM.path)  
16.          -- 路径=自己的路径移除原来经理那部分再加上新的经理的那部分  
17.          --这里的 OM.path 是被替换经理的经理的路径 比如旧经理是1-3-7 那么OM.PATH 就是1-3   
18.          --NM.path 是新经理的路径了 比如要换新经理的EMPID是10 所以NM.path  就是 1-2-5-10  
19.          --所以如果本来旧经理一个手下比如说是1-3-7-10 现在整个替换过来就是1-2-5-10-7-10  
20.   FROM dbo.Employees AS E          -- E = 员工  
21.     JOIN dbo.Employees AS R        -- R = 根  
22.       ON R.empid = @root  
23.       AND E.path LIKE R.path + '%'  
24.     JOIN dbo.Employees AS OM       -- OM = 旧经理  
25.       ON OM.empid = R.mgrid  
26.     JOIN dbo.Employees AS NM       -- NM = 新经理  
27.       ON NM.empid = @mgrid;  
28.   
29.   -- 更新旧的经理的经理为新来的经理  
30.   UPDATE dbo.Employees SET mgrid = @mgrid WHERE empid = @root;  
31.     
32. COMMIT TRAN;  
33. GO  
34.   
35.   
36. BEGIN TRAN;  
37.   
38.   EXEC dbo.usp_movesubtree  
39.   @root  = 7,  
40.   @mgrid = 10;  
41.   
42.   -- After moving subtree  
43.   SELECT empid, REPLICATE(' | ', lvl) + empname AS empname, lvl, path  
44.   FROM dbo.Employees  
45.   ORDER BY path;  
46.   
47. ROLLBACK TRAN;   

--移动后结果

 

[c-sharp]  view plain copy print ?

1. /* 
2. empid       empname                            lvl         path                   
3.  
4. ----------- ------------------------------ ----------- ---------------------- 
5. 1           David                              0           1                       
6. 2            | Eitan                           1           1-2                          
7. 4            |  | Seraph                       2           1-2-4              
8. 5            |  | Jiru                         2           1-2-5              
9. 10           |  |  | Sean                      3           1-2-5-10          
10. 7            |  |  |  | Aaron                  4           1-2-5-10-7        
11. 11           |  |  |  |  | Gabriel             5           1-2-5-10-7-11    
12. 9            |  |  |  |  | Rita                5           1-2-5-10-7-9            
13. 12           |  |  |  |  |  | Emilia           6           1-2-5-10-7-9-12     
14. 13           |  |  |  |  |  | Michael          6           1-2-5-10-7-9-13     
15. 14           |  |  |  |  |  | Didi             6           1-2-5-10-7-9-14      
16. 8            |  |  | Lilach                    3           1-2-5-8            
17. 6            |  | Steve                        2           1-2-6   
18. 3            | Ina                             1           1-3      
19.  
20.                                                             
21. */  

--这个是移动之后 大家注意观察7 和 10 两位同志

 

3.移除子树
就是把某个部门从公司取消掉
--首先查看公司部门当前分布

[c-sharp]  view plain copy print ?

1. SELECT empid, REPLICATE(' | ', lvl) + empname AS empname, lvl, path  
2. FROM dbo.Employees  
3. ORDER BY path;  

 

--查询结果

[c-sharp]  view plain copy print ?

1. /* 
2. empid       empname             lvl         path         
3.  
4. ----------- -------------   -------------  ----------- 
5. 1           David                0           1           
6. 2            | Eitan             1           1-2       
7. 4            |  | Seraph         2           1-2-4         
8. 5            |  | Jiru           2           1-2-5         
9. 8            |  |  | Lilach      3           1-2-5-8     
10. 6            |  | Steve          2           1-2-6          
11. 3            | Ina               1           1-3                   
12. 7            |  | Aaron          2           1-3-7       
13. 11           |  |  | Gabriel     3           1-3-7-11   
14. 9            |  |  | Rita        3           1-3-7-9              
15. 12           |  |  |  | Emilia   4           1-3-7-9-12    
16. 13           |  |  |  | Michael  4           1-3-7-9-13  
17. 14           |  |  |  | Didi     4           1-3-7-9-14     
18. */  

--接着我们来开始一个部门 比如移除Aaron手下的人

[c-sharp]  view plain copy print ?

1. BEGIN TRAN;  
2.   
3.   DELETE FROM dbo.Employees  
4.   WHERE path LIKE  
5.     (SELECT M.path + '%'  
6.      FROM dbo.Employees as M  
7.      WHERE M.empid = 7);  
8.   
9.   --删除之后显示  
10.   SELECT empid, REPLICATE(' | ', lvl) + empname AS empname, lvl, path  
11.   FROM dbo.Employees  
12.   ORDER BY path;  
13.   
14. ROLLBACK TRAN;  

 

--查询结果

[c-sharp]  view plain copy print ?

1. /* 
2. empid       empname               lvl         path                    
3.  
4. ----------- ------------------ ----------- ------------ 
5. 1           David                 0           1                        
6. 2            | Eitan              1           1-2                      
7. 4            |  | Seraph          2           1-2-4       
8. 5            |  | Jiru            2           1-2-5       
9. 10           |  |  | Sean         3           1-2-5-10    
10. 8            |  |  | Lilach       3           1-2-5-8     
11. 6            |  | Steve           2           1-2-6   
12. 3            | Ina                1           1-3 
13. */  

 

4.查询

这里的查询可就轻松多啦 ~因为路径都有啦~
--查询EMPID为3的手下一批人

[c-sharp]  view plain copy print ?

1. SELECT REPLICATE(' | ', E.lvl - M.lvl) + E.empname  
2. FROM dbo.Employees AS E  
3.   JOIN dbo.Employees AS M  
4.     ON M.empid = 3 -- root  
5.     AND E.path LIKE M.path + '%'  
6.    --and  E.path LIKE M.path + '_%'--这里那个3号就没了  
7.    --and  E.lvl - M.lvl <= 2 --限制级数  
8.    --WHERE NOT EXISTS        --自己本身不是经理的,返回的都是员工  
9.    (SELECT *  
10.      FROM dbo.Employees AS E2  
11.      WHERE E2.mgrid = E.empid);  
12. ORDER BY E.path;  

 

--查询结果

[c-sharp]  view plain copy print ?

1. /* 
2. ------------------ 
3. Ina 
4.  | Aaron 
5.  |  | Gabriel 
6.  |  | Rita 
7.  |  |  | Emilia 
8.  |  |  | Michael 
9.  |  |  | Didi 
10.  
11. */