sql选出层级树（转）

最新推荐文章于 2024-03-26 13:40:10 发布
最新推荐文章于 2024-03-26 13:40:10 发布
阅读量994 收藏
点赞数
分类专栏： 数据库 文章标签： sql 树 tree path

Posted on 2012-12-08 09:54 cunriqian 阅读(387) 评论(0) 编辑 收藏

在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. */  

 

 

b.嵌套集合
是书的作者认为用于树建模最完美最高明的解决方案.
惭愧..太难..准备翻第二次书的时候再研究...

Hive与Spark SQL原理详解
zhengzaifeidelushang的博客
08-04 943
Hive与Spark SQL 开源社区在分布式计算框架基础上构建了支持SQL的引擎，典型的代表是MapReduce之上的Hive以及Spark之上的Spark SQL，这些数据分析引擎通常不支持标准SQL，而是对SQL进行了选择性支持，并进行了适当扩展，其中最主流的数据分析语言为HQL(Hive Query Language）。 一、SQL On Hadoop 目前构建在Hadoop之上的SQL引擎主要分为两类，基于计算引擎和基于MPP架构： 基于计算引擎：SQL引擎是在计算引擎基础上构建的，其基本原理
树，图，层次结构在sql中的巧妙使用（一）
qq_37617419的博客
03-23 1471
一.图 图是一组由边连接起来的项目组成的集合。每一项称为图的一个节点。图的两个顶点之间的连接称为边。 图是对一类数据结构的统称，很多应用场景都可以表示为图。例如：下面要介绍的员工组织图，材料清单图，道路系统等等。 有向/无向 在有向图中，一条边的两个顶点具有某种方向或顺序。无向图中，每条边只是简单连接两个顶点，没有特定顺序。 无环 无环图是指不包含循环的图。意思是，图中没有任何路径开始和结束于同一个顶点。（例如。员工组织图和BOM就是无环图。有向无环图也称为DAG） ...
SQL 中的 图 树 层次结构[转自http://blog.csdn.net/feixianxxx/article/details/4753783]
ASP.NET
11-22 1138
在RDBMS中操作 图 树 层次结构 等特殊的数据结构时,我们通常采用2个主要方法: 1.基于迭代/递归 2.具体化描述数据结构的附加信息。 一般模型有：员工组织图(树,层次结构);料表--BOM(有向图);道路系统(无向循环图) 1.迭代/递归 迭代可以迭代图的一个节点,也可以迭代一个层次.后者比前者要快很多. 实现方法：SQL2000通过UDF(用户自定义函数),SQL
sqlserver的树形查询
codepython的专栏
09-23 3279
SQL Server没有ORACLE那样专门的树形查询语法，而这却是很多数据库模型中要用到的，比如主机结构，产品分类等。 我查询了很多资料没有一个满意的，基本有3中方案： 1. 简单的带with的递归查询，无法排序，更加无法显示体系架构 2 使用ROW_NUMBER()over功能，比较完美，但某些极端情况不能工作，复杂难懂 3 老外有存储过程来实现，很完美，但建立新的对象很不爽 我
在SQL中高效组织层次数据
11-23
在SQL数据库中高效组织层次数据的查询，将原来需要嵌套查询的数据通过parent_left,parent_right的方法处理。
使用sql查询部门表中某一个部门的下级部门,部门表是树结构有pid；
最新发布
m0_61891910的博客
03-26 1291
需求:给出一个部门的id,查找这个部门(包括这个部门)的所有下级部门的id.例如某个部门id为2,下级部门有3,4,5,6,7,则查找结果应该是:2,3,4,5,6,7.这个需求的作用是统计该部门下的所有的用户数量.这段 SQL 查询中的 IF 语句用于在查询结果中创建一个名为 ischild 的列，其值取决于条件的成立与否。总体来说，这个查询的目的是选择部门的 ID，并且标识出哪些部门是子部门，以便在查询结果中进行进一步的处理或展示。的值追加到变量 @pids 的末尾，以逗号分隔不同的部门ID。
mysql二维表格转树 如何写sql
06-08
将二维表格转为树形结构的 SQL 查询语句，可以使用 MySQL 的 WITH RECURSIVE 语句实现。 假设我们有一个名为 `category` 的表格，包含以下字段： - `id`: 分类 ID - `name`: 分类名称 - `parent_id`: 父级分类 ID ...
sql树形数据处理示例(Jet wang 最新整理)
02-27
在SQL数据库中，树形数据...通过学习和实践这些方法，我们可以有效地处理和查询SQL中的树形数据，无论是简单的分类树还是复杂的多层级结构。 Jet Wang的示例将提供具体的代码实例，帮助我们更好地理解和应用这些概念。
SQL Server 索引维护（1）——如何获取索引使用情况
热门推荐
MVP黄钊吉（發糞塗牆）
11-09 1万+
前言： 在前面一文中，已经提到了三类常见的索引问题，那么问题来了，当系统出现这些问题时，该如何应对？ 简单而言，需要分析现有系统的行为，然后针对性地对索引进行处理：对于索引不足的情况：检查缺少索引的情况，也需要检查现有索引定义是否有问题。对于索引过多的情况：分析每一个索引的使用情况，判断是否有存在的必要或者可合并、可修改的可能。对于索引不合理的情况：也要分析每个索引的定义，及其使用情况，确定索引是
用SQL Server显示树结构, 不用递归算法，不用游标，没有层次限制， 速度很快
08-02
-- 用SQL Server显示树结构, 不用递归算法，不用游标，没有层次限制， 速度很快 -- 显示、打印用起来很方便的 -- select * from Dept -- select * from dbo.GetDeptTree('00000000-0000-0000-0000-000000000000') dp order by dp.Seq
层次遍历的两种方式：递归与迭代
列苗
08-21 2931
二叉树的层次遍历方式一：递归调用（参考网上的方法），先序遍历递归时多传递一个层次数，按照该层次数保存节点相关值。 private List> resList = new ArrayList>(); public static void levelTraversal(int dep, TreeNode root) { if(root == null) { return ; } if(
获取部门树（ 上篇 ）：直接取出一整棵部门树
DD-Blog
06-15 470
该部门表，可能有些童鞋疑惑为什么父级id可以重复但是子级id没有重复呢？ 这就像一个部门下可以有多个子部门，但是一个子部门不能对应多个父级部门。 一个父亲可以有多个孩子，一个孩子不能有多个父亲。 如果不加这个约束会带来很多管理的困难。 第一步：如果想获取整棵树，先把根结点-1取出。 第二步：再用-1去获取子级id 1和2 第三步：再用 1和2 去获取 3、4、5、6 第四步：再用3、4、5、6 去获取 。。。。 。。。 这个道理谁都懂，但是具体用什么方法，怎么样去取呢？ 面对这种类型的问题，第一步到第N步.
sql的树形结构遍历所有子节点
weixin_39674487的博客
10-16 4603
sqlServer树形查询 with f as ( select * from view_zz where TEAM='90' union all select a.* from view_zz as a inner join f as b on a.DEP_PARENTID=b.TEAM ) select * from f oracle树形查询 SELECT a.*,org_name ...
SQL获取部门/目录（树形结构）等全路径
烧硬盘
12-29 2604
SQL获取目录全路径 下列是获取目录全路径，也适用于部门以及树形结构的数据展现形式 with cte as( select fd_id,cast(fd_name as nvarchar(max)) as allpath from kms_knowledge_category where fd_parent_id is null union all select sub.fd_id,parent.allpath+'-'+sub.fd_name from cte parent inner join km
使用SQL Server 2008中的hierarchyid类型来设计具有树型层次关系的表
灰太狼的博客
07-30 8584
本文主要讲述三个内容： 1.如何创建hierarychyid的表，插入数据及基本递归查询。 2.介绍hierarchyid的10种专有函数。 3.介绍hierarchyid特有的深度优先索引(Depth-First Indexing)和广度优先索引(Breadth-First Indexing) 在上一节中 http://blog.csdn.net/tjvictor/archive/200
通过sql生成树型结构
eidolon's blog
07-11 3127
create   table   tb(id   int,name   varchar(10),parentid   int)     insert   tb   select   1,aaaa         ,0     union   all   select   2,bbbb         ,0     union   all   select   3,cccc     
sqlserver中一个表中树形结构递归数据查询
四点的博客
01-26 2561
需求：一个数据表是树形结构，即id，pid，每条记录会有自己的父级记录。现在要一下把指定记录以及该记录为父级时，所有下级层级记录查询出来。 解决：这里面用到了sqlserver的with as语句以及union all合并 WITH Emp AS ( SELECT * FROM dbo.Users WHERE ID = '111'
SQL递归查询行转列
wallelhw的博客
12-04 401
由于工作环境中需要在SQL环境中实现树表到节点分级表的转换，引用链接中实例链接在此 #Categorytree表： ID ParentID CategoryID 1 0 1 2 0 2 3 1 3 4 3 4 5 2 7 #Category表： CategoryID CategoryName LevelID 1 企划 1 2 开发 1 3 年...
SQL行转列与列转行操作详解
"SQL中如何实现行转列，列转行的操作" 在SQL数据库查询中，行转列和列转行是常见的数据处理需求，主要应用于数据报表的生成和数据分析。这里我们将通过案例来详细解释如何使用SQL实现这两种转换。 1. 行转列 行转...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值