ASP源码.NET源码PHP源码JSP源码JAVA源码DELPHI源码PB源码VC源码VB源码Android源码
当前位置:首页 >> 数据库 >> SqlServer >> SQL 中的 图 树 层次结构[转自http://blog.csdn.net/feixianxxx/article/details/4753783]

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

来源:网络整理     时间:2014-12-11     关键词:

本篇文章主要介绍了"SQL 中的 图 树 层次结构[转自http://blog.csdn.net/feixianxxx/article/details/4753783]",主要涉及到方面的内容,对于SqlServer感兴趣的同学可以参考一下: 在RDBMS中操作 图 树 层次结构 等特殊的数据结构时,我们通常采用2个主要方法:1.基于迭代/递归2.具体化描述数据结构的附加信息。一般模型有:员工组织图(...

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

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


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

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

[c-sharp] view plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

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

--sql2005 

[c-sharp] view plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

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

--查询结果

[c-sharp] view plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

  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 plaincopyprint?

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

以上就介绍了SQL 中的 图 树 层次结构[转自http://blog.csdn.net/feixianxxx/article/details/4753783],包括了方面的内容,希望对SqlServer有兴趣的朋友有所帮助。

本文网址链接:http://www.codes51.com/article/detail_98601.html

相关图片

相关文章