在线跪求一个存储过程急用

Question

我们在工作中经常会遇到字典管理,字典管理的表Dictionary结构如下: 
SN(主键) FKSN(外键)ID(编号) Title(标题) 
1 0 001 信息中心 
2 0 002 业务中心 
3 1 001 人员管理 
4 1 002 产品管理 
5 1 003 帐户管理 
6 2 001 销售管理 
7 2 002 撤单管理 
8 2 003 退货管理 
9 3 001 性别字典 
10 6 001 结算字典 
11 9 001 男 
12 9 002 女 
13 10 001 现金 
14 10 002 欠款 
这样就构造成了一棵以FKSN=0为根节点的树。 
给定任何一个节点的SN 请查询出所有的子节点。（如果SN=13则应该查出“现金” 如果SN=10则查出“结算字典”“现金”和“欠款” 如SN=1 则查出“信息中心” “人员管理” “产品管理” “帐户管理” “性别字典” “男” “女”） 
要求写一个接收SN为参数，返回所有子节点的存储过程。 
DECLARE @SN INT 
SET @SN = 2 
DECLARE @Queue TABLE(AutoSN INT IDENTITY(1,1),SN INT) 
DECLARE @Visited TABLE(SN INT) 
INSERT INTO @Queue(SN) 
SELECT SN FROM dbo.DictionaryBAK 
WHERE SN = @SN 
DECLARE @CurrentSN INT 
WHILE ((SELECT count(*) FROM @Queue) > 0) 
BEGIN 
SELECT TOP 1 @CurrentSN = SN 
FROM @Queue 
INSERT INTO @Visited(SN) 
VALUES(@CurrentSN) 
DELETE FROM @Queue 
WHERE SN = @CurrentSN 
INSERT INTO @Queue(SN) 
SELECT SN FROM dbo.DictionaryBAK 
WHERE FKSN = @CurrentSN 
END 
SELECT * FROM dbo.DictionaryBAK 
WHERE SN IN (SELECT DISTINCT SN FROM @Visited) 结合上面的存储过程来看下面的问题： 
我们在工作中经常会遇到字典管理,字典管理(Dictionary)的表结构如下: 
SN(主键) FKSN(外键)ID(编号) Title(标题)CompanySN(公司) 
1 0 001 信息中心  44 
2 0 002 业务中心         44 
3 1 001 人员管理         44 
4 1 002 产品管理         44 
5 1 003 帐户管理         44 
6 2 001 销售管理         44 
7 2 002 撤单管理         44 
8 2 003 退货管理         44 
9 3 001 性别字典         44 
10 6 001 结算字典         44 
11 9 001 男         44 
12 9 002 女         44 
13 10 001 现金         44 
14 10 002 欠款         44 
这样就构造成了一棵以FKSN=0为根节点的树。 
我们经常会复制节点，比如在开发环境中复制注册表，在创建公司的时候把我们公司的所有的字典项复制到要创建公司，其实就是把选定的所有子节点重新生成SN和FKSN然后插入到表中。现要求写一存储过程，接收参数(1).复制节点的SN (2).要存放的目标节点的DestSN (3).目标公司关键字CompanySN, 将要复制节点的所有子节点复制到目标公司的目标节点下。
表我已经在数据库里面创建完了，不用临时表，直接在数据库里面书写，第二题是第一题的延伸，谢谢大家了

Answer 1

楼主又来一个贴..晕
贴个资料你自己参考下吧
/*
RDBMS中操作S图树层次结构等特殊的数据结构时,我们通常采用个主要方法:
一个是基于迭代/递归另外一个是具体化描述数据结构的附加信息。
一般模型有：员工组织图(树,层次结构);料表--BOM(有向图);道路系统(无向循环图)
1.迭代/递归
迭代可以迭代图的一个节点,也可以迭代一个层次.后者比前者要快很多.
实现方法：SQL2000通过UDF(用户自定义函数),SQL2005使用CTE。a.下属问题(通俗说,求子节点)
*/
--这里我使用书上的员工表
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.Employees') IS NOT NULL
  DROP TABLE dbo.Employees;
GO
CREATE TABLE dbo.Employees
(
  empid   INT         NOT NULL PRIMARY KEY,
  mgrid   INT         NULL     REFERENCES dbo.Employees,
  empname VARCHAR(25) NOT NULL,
  salary  MONEY       NOT NULL,
  CHECK (empid <> mgrid)
);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(1, NULL, 'David', $10000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(2, 1, 'Eitan', $7000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(3, 1, 'Ina', $7500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(4, 2, 'Seraph', $5000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(5, 2, 'Jiru', $5500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(6, 2, 'Steve', $4500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(7, 3, 'Aaron', $5000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(8, 5, 'Lilach', $3500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(9, 7, 'Rita', $3000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(10, 5, 'Sean', $3000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(11, 7, 'Gabriel', $3000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(12, 9, 'Emilia' , $2000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(13, 9, 'Michael', $2000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(14, 9, 'Didi', $1500.00);
--创建索引
CREATE UNIQUE INDEX idx_unc_mgrid_empid ON dbo.Employees(mgrid, empid);
go--SQL2000 udf方法：
IF OBJECT_ID('dbo.fn_subordinates1') IS NOT NULL
  DROP FUNCTION dbo.fn_subordinates1;
GO
CREATE FUNCTION dbo.fn_subordinates1(@root AS INT) 
RETURNS @Subs TABLE
(
  empid INT NOT NULL PRIMARY KEY NONCLUSTERED,
  lvl   INT NOT NULL,
  UNIQUE CLUSTERED(lvl, empid)  
)
AS
begin 
declare @lv int 
set @lv=0
insert @Subs values(@root,@lv)
while @@rowcount>0
begin 
    set @lv=@Lv+1;
    insert @subs
    select b.empid ,@Lv
    from @subs a join dbo.Employees b on a.empid=b.mgrid and lvl=@lv-1
end
return;
end 
go
SELECT empid, lvl FROM dbo.fn_subordinates1(3) AS S;--SQL2005 CTE
DECLARE @root AS INT;
SET @root = 3;
WITH SubsCTE
AS
(
  -- Anchor member returns root node
  SELECT empid, empname, 0 AS lvl
  FROM dbo.Employees
  WHERE empid = @root  UNION ALL  -- Recursive member returns next level of children
  SELECT C.empid, C.empname, P.lvl + 1
  FROM SubsCTE AS P
    JOIN dbo.Employees AS C
      ON C.mgrid = P.empid
)
SELECT * FROM SubsCTE;
/*
empid       empname                   lvl
----------- ------------------------- -----------
3           Ina                       0
7           Aaron                     1
9           Rita                      2
11          Gabriel                   2
12          Emilia                    3
13          Michael                   3
14          Didi                      3*/
---------------如果需要限制递归的层数------------
--SQL2000 IF OBJECT_ID('dbo.fn_subordinates2') IS NOT NULL
  DROP FUNCTION dbo.fn_subordinates2;
GO
CREATE FUNCTION dbo.fn_subordinates2
  (@root AS INT, @maxlevels AS INT = NULL) RETURNS @Subs TABLE
(
  empid INT NOT NULL PRIMARY KEY NONCLUSTERED,
  lvl   INT NOT NULL,
  UNIQUE CLUSTERED(lvl, empid)  
)
AS
BEGIN
  DECLARE @lvl AS INT;
  SET @lvl = 0;            
  -- 如果@maxlevels输入的是NULL,把他变为最大的INT类型
 SET @maxlevels = COALESCE(@maxlevels, 2147483647);
  INSERT INTO @Subs(empid, lvl)
    SELECT empid, @lvl FROM dbo.Employees WHERE empid = @root;  WHILE @@rowcount > 0         
    AND @lvl < @maxlevels       -- 这里注意加一个小于最大的递归数的条件,是小于不是小于等于
  BEGIN
    SET @lvl = @lvl + 1;            INSERT INTO @Subs(empid, lvl)
      SELECT C.empid, @lvl               --这里跟上面处理都一样
      FROM @Subs AS P           
        JOIN dbo.Employees AS C 
          ON P.lvl = @lvl - 1  
          AND C.mgrid = P.empid;
  END  RETURN;
END
GO
SELECT empid, lvl
FROM dbo.fn_subordinates2(3, NULL) AS S;
/*
empid       lvl
----------- -----------
3           0
7           1
9           2
11          2
12          3
13          3
14          3
*/
-----
SELECT empid, lvl
FROM dbo.fn_subordinates2(2, NULL) AS S;
/*
empid       lvl
----------- -----------
2           0
4           1
5           1
6           1
8           2
10          2
*/
PS：这里控制返回的层数你当然可以用最开始的方法,然后再筛选语句里控制层数
 如SELECT empid, lvl FROM dbo.fn_subordinates1(3) AS S where lvl<3;  但是控制本身的递归只能在UDF里面了
 
 --sql2005方法类似
 WITH SubsCTE
AS
(
  SELECT empid, empname, 0 AS lvl
  FROM dbo.Employees
  WHERE empid = @root
  UNION ALL  SELECT C.empid, C.empname, P.lvl + 1
  FROM SubsCTE AS P
    JOIN dbo.Employees AS C
      ON C.mgrid = P.empid
      AND P.lvl < @maxlevels -- 这里控制递归数
)
SELECT * FROM SubsCTE;--还有一种偏方：但是不推荐虽然结果正确但是会报错
WITH SubsCTE
AS
(
  SELECT empid, empname, 0 AS lvl
  FROM dbo.Employees
  WHERE empid = @root  UNION ALL  SELECT C.empid, C.empname, P.lvl + 1
  FROM SubsCTE AS P
    JOIN dbo.Employees AS C
      ON C.mgrid = P.empid
)
SELECT * FROM SubsCTE
OPTION (MAXRECURSION 2);--就是这里的MAXRECURSION 控制递归
 /*
 empid       empname                   lvl
----------- ------------------------- -----------
3           Ina                       0
7           Aaron                     1
9           Rita                      2
11          Gabriel                   2
消息530，级别16，状态1，第4 行
语句被终止。完成执行语句前已用完最大递归2。
    */
    
/*
b.祖先(通俗说：求父节点)
其实思路跟子节点差不多
*/
--SQL2000
IF OBJECT_ID('dbo.fn_managers') IS NOT NULL
  DROP FUNCTION dbo.fn_managers;
GO
CREATE FUNCTION dbo.fn_managers
(@empid AS INT, @maxlevels AS INT = NULL) RETURNS @Mgrs TABLE
(
  empid INT NOT NULL PRIMARY KEY,
  lvl   INT NOT NULL
)
AS
BEGIN
  IF NOT EXISTS(SELECT * FROM dbo.Employees WHERE empid = @empid) --这里判断是否存在经理,不存在马上返回
    RETURN;  DECLARE @lvl AS INT,@mgrid int ;
  SET @lvl = 0;  
  set @mgrid=@empid  --赋值给@mgrid 我这是为了更加清楚变量的意思其实不用这个@Mgrid变量的           -- 如果@maxlevels输入的是NULL,把他变为最大的INT类型
  SET @maxlevels = COALESCE(@maxlevels, 2147483647);  WHILE  @lvl <= @maxlevels and @mgrid is not null     --注意这里是小于等于而且新插入的经理不可以为NULL，为null说明是老大了
  BEGIN
    --插入当前经理
    INSERT INTO @Mgrs(empid, lvl) VALUES(@mgrid, @lvl); --这里第一次插入是自己,后面就是各自的经理了
    SET @lvl = @lvl + 1;        
    --获得下一个级别的经理
    SET @mgrid = (SELECT mgrid FROM dbo.Employees
                  WHERE empid = @mgrid);
  
  END  RETURN;
END
GO
SELECT empid, lvl
FROM dbo.fn_managers(8, NULL) AS M;
/*
empid       lvl
----------- -----------
1           3
2           2
5           1
8           0*/
SELECT empid, lvl
FROM dbo.fn_managers(8, 2) AS M;
/*
empid       lvl
----------- -----------
2           2
5           1
8           0
*/--sql2005 
DECLARE @empid AS INT, @maxlevels AS INT;
SET @empid = 8;
SET @maxlevels = 2;WITH MgrsCTE
AS
(
  SELECT empid, mgrid, empname, 0 AS lvl
  FROM dbo.Employees
  WHERE empid = @empid  UNION ALL  SELECT P.empid, P.mgrid, P.empname, C.lvl + 1
  FROM MgrsCTE AS C
    JOIN dbo.Employees AS P
      ON C.mgrid = P.empid
      AND C.lvl < @maxlevels--限制递归数...
)
SELECT * FROM MgrsCTE;

Answer 2

/*
C.层次显示
*/
--SQL2000,思路跟下属问题一模一样只是多了个PATH
IF OBJECT_ID('dbo.fn_subordinates3') IS NOT NULL
  DROP FUNCTION dbo.fn_subordinates3;
GO
CREATE FUNCTION dbo.fn_subordinates3
  (@root AS INT, @maxlevels AS INT = NULL) RETURNS @Subs TABLE
(
  empid INT          NOT NULL PRIMARY KEY NONCLUSTERED,
  lvl   INT          NOT NULL,
  path  VARCHAR(900) NOT NULL
  UNIQUE CLUSTERED(lvl, empid) 
)
AS
BEGIN
  DECLARE @lvl AS INT;
  SET @lvl = 0;                  SET @maxlevels = COALESCE(@maxlevels, 2147483647);
  INSERT INTO @Subs(empid, lvl, path)
    SELECT empid, @lvl, CAST(empid AS VARCHAR(100))--ZHU YI ZHE LI 
    FROM dbo.Employees 
    WHERE empid = @root;  WHILE @@rowcount > 0          
    AND @lvl < @maxlevels       
  BEGIN
    SET @lvl = @lvl + 1;       
    INSERT INTO @Subs(empid, lvl, path)
      SELECT C.empid, @lvl,
        P.path +'-'+ CAST(C.empid AS VARCHAR(100)) --和上面类型保持一致
      FROM @Subs AS P           
        JOIN dbo.Employees AS C 
          ON P.lvl = @lvl - 1  
          AND C.mgrid = P.empid;
  END  RETURN;
END
GO
--这里的显示分种
--显示
select empid ,pos=REPLICATE('-',lvl)+rtrim(empid)
FROM dbo.fn_subordinates3(1, NULL) AS S 
ORDER BY PATH 
/*
empid       pos
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           1
2           -2
4           --4
5           --5
10          ---10
8           ---8
6           --6
3           -3
7           --7
11          ---11
9           ---9
12          ----12
13          ----13
14          ----14
*/
--还有一种
SELECT empid,  path
FROM dbo.fn_subordinates3(1, NULL) AS S
ORDER BY PATH 
/*
empid       path
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           1
2           1-2
4           1-2-4
5           1-2-5
10          1-2-5-10
8           1-2-5-8
6           1-2-6
3           1-3
7           1-3-7
11          1-3-7-11
9           1-3-7-9
12          1-3-7-9-12
13          1-3-7-9-13
14          1-3-7-9-14
*/--当然上面的显示方式还很多,自己可以控制比如不加ORDER 就可以排出不一样的--SQL2005
DECLARE @root AS INT;
SET @root = 1;WITH SubsCTE
AS
(
  SELECT empid, empname, 0 AS lvl,
    -- Path of root = '.' + empid + '.'
    CAST(CAST(empid AS VARCHAR(10)) 
         AS VARCHAR(MAX)) AS path
  FROM dbo.Employees
  WHERE empid = @root  UNION ALL  SELECT C.empid, C.empname, P.lvl + 1,    CAST(P.path+'-' + CAST(C.empid AS VARCHAR(10)) 
         AS VARCHAR(MAX)) AS path
  FROM SubsCTE AS P
    JOIN dbo.Employees AS C
      ON C.mgrid = P.empid
)
SELECT empid, REPLICATE(' | ', lvl) + empname AS empname
FROM SubsCTE
ORDER BY path;
/*  
empid       empname  
----------- -------------------------  
1           David  
2            | Eitan  
4            |  | Seraph  
5            |  | Jiru  
10           |  |  | Sean  
8            |  |  | Lilach  
6            |  | Steve  
3            | Ina  
7            |  | Aaron  
11           |  |  | Gabriel  
9            |  |  | Rita  
12           |  |  |  | Emilia  
13           |  |  |  | Michael  
14           |  |  |  | Didi  
 
*/  /*
D.检测循环中异常
说白了就是检查表里有没出现-2-4-1这种头接尾的圈.这在现实中是不可能的.一个经理部可能是它手下的手下.-- ||
我们对表做手脚,把老大的经理改成是它的一个员工
*/
UPDATE dbo.Employees SET mgrid = 14 WHERE empid = 1;
DECLARE @root AS INT;
SET @root = 1;WITH SubsCTE
AS
(
  SELECT empid, empname, 0 AS lvl,
    CAST( CAST(empid AS VARCHAR(10))
         AS VARCHAR(MAX)) AS path,
    -- 第一层是不会出现圈圈的
    0 AS cycle --0表示没有圈1表示出现圈
  FROM dbo.Employees
  WHERE empid = @root  UNION ALL  SELECT C.empid, C.empname, P.lvl + 1,
    CAST(P.path + '-'+CAST(C.empid AS VARCHAR(10)) 
         AS VARCHAR(MAX)) AS path,
    -- 这里需要检查
    CASE WHEN P.path LIKE '%' + CAST(C.empid AS VARCHAR(10)) + '%'
      THEN 1 ELSE 0 END
  FROM SubsCTE AS P
    JOIN dbo.Employees AS C
      ON C.mgrid = P.empid
   where p.cycle=0 --保证不会继续在错误的地方继续循环下去
)
SELECT empid, empname, cycle, path
FROM SubsCTE
where cycle=1
/*  
empid       empname                   cycle       path  ----------- ------------------------- ----------- ------------------  
1           David                     1           1-3-7-9-14-1  
 
*/
--这样管理员可以轻易找到那个错误的地方.
/*
改过来：UPDATE dbo.Employees SET mgrid = NULL WHERE empid = 1;

Answer 3

2.具体化路径
这里就是新增加列，一列是级别一列是节点路径，这样可以避免每次都去计算.
2个优点：不需要递归,只需要基于集合；查询可以使用到路径索引a.维护数据
1.添加不管理员工的员工
*/
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.Employees') IS NOT NULL
  DROP TABLE dbo.Employees;
GO
CREATE TABLE dbo.Employees
(
  empid   INT          NOT NULL PRIMARY KEY NONCLUSTERED,
  mgrid   INT          NULL     REFERENCES dbo.Employees,
  empname VARCHAR(25)  NOT NULL,
  salary  MONEY        NOT NULL,
  lvl     INT          NOT NULL,
  path    VARCHAR(900) NOT NULL UNIQUE CLUSTERED
);
CREATE UNIQUE INDEX idx_unc_mgrid_empid ON dbo.Employees(mgrid, empid);
GO
IF OBJECT_ID('dbo.usp_insertemp') IS NOT NULL
  DROP PROC dbo.usp_insertemp;
GO
CREATE PROC dbo.usp_insertemp
  @empid   INT,
  @mgrid   INT,
  @empname VARCHAR(25),
  @salary  MONEY
ASSET NOCOUNT ON;-- 如果插入的是一个没有经理的老大
IF @mgrid IS NULL
  INSERT INTO dbo.Employees(empid, mgrid, empname, salary, lvl, path)
    VALUES(@empid, @mgrid, @empname, @salary,
      0,  CAST(@empid AS VARCHAR(10)));
--插入有经理的小弟
ELSE
  INSERT INTO dbo.Employees(empid, mgrid, empname, salary, lvl, path)
    SELECT @empid, @mgrid, @empname, @salary,
      lvl + 1, path +'-'+ CAST(@empid AS VARCHAR(10)) 
    FROM dbo.Employees
    WHERE empid = @mgrid;
GOEXEC dbo.usp_insertemp
  @empid = 1, @mgrid = NULL, @empname = 'David', @salary = $10000.00;
EXEC dbo.usp_insertemp
  @empid = 2, @mgrid = 1, @empname = 'Eitan', @salary = $7000.00;
EXEC dbo.usp_insertemp
  @empid = 3, @mgrid = 1, @empname = 'Ina', @salary = $7500.00;
EXEC dbo.usp_insertemp
  @empid = 4, @mgrid = 2, @empname = 'Seraph', @salary = $5000.00;
EXEC dbo.usp_insertemp
  @empid = 5, @mgrid = 2, @empname = 'Jiru', @salary = $5500.00;
EXEC dbo.usp_insertemp
  @empid = 6, @mgrid = 2, @empname = 'Steve', @salary = $4500.00;
EXEC dbo.usp_insertemp
  @empid = 7, @mgrid = 3, @empname = 'Aaron', @salary = $5000.00;
EXEC dbo.usp_insertemp
  @empid = 8, @mgrid = 5, @empname = 'Lilach', @salary = $3500.00;
EXEC dbo.usp_insertemp
  @empid = 9, @mgrid = 7, @empname = 'Rita', @salary = $3000.00;
EXEC dbo.usp_insertemp
  @empid = 10, @mgrid = 5, @empname = 'Sean', @salary = $3000.00;
EXEC dbo.usp_insertemp
  @empid = 11, @mgrid = 7, @empname = 'Gabriel', @salary = $3000.00;
EXEC dbo.usp_insertemp
  @empid = 12, @mgrid = 9, @empname = 'Emilia', @salary = $2000.00;
EXEC dbo.usp_insertemp
  @empid = 13, @mgrid = 9, @empname = 'Michael', @salary = $2000.00;
EXEC dbo.usp_insertemp
  @empid = 14, @mgrid = 9, @empname = 'Didi', @salary = $1500.00;
----检测
SELECT empid, mgrid, empname, salary, lvl, path
FROM dbo.Employees
ORDER BY path;
/*
empid       mgrid       empname                   salary                lvl         path
----------- ----------- ------------------------- --------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           NULL        David                     10000.00              0           1
2           1           Eitan                     7000.00               1           1-2
4           2           Seraph                    5000.00               2           1-2-4
5           2           Jiru                      5500.00               2           1-2-5
10          5           Sean                      3000.00               3           1-2-5-10
8           5           Lilach                    3500.00               3           1-2-5-8
6           2           Steve                     4500.00               2           1-2-6
3           1           Ina                       7500.00               1           1-3
7           3           Aaron                     5000.00               2           1-3-7
11          7           Gabriel                   3000.00               3           1-3-7-11
9           7           Rita                      3000.00               3           1-3-7-9
12          9           Emilia                    2000.00               4           1-3-7-9-12
13          9           Michael                   2000.00               4           1-3-7-9-13
14          9           Didi                      1500.00               4           1-3-7-9-14*//*
2.移动子树
比如说某个部门来了个新老大,原来部门老大和手下的人都要跟着他.这个时候表里的路径和级别都要更新
*/
Select Empid, Replicate(' | ', Lvl) + Empname As Empname, Lvl, Path
From Dbo.Employees
Order By Path;
/*  
empid       empname             lvl             path   
 
----------- ----------------- ----------- ------------  
1           David                   0           1                       
2            | Eitan                1           1-2  
4            |  | Seraph            2           1-2-4                       
5            |  | Jiru              2           1-2-5             
10           |  | | Sean            3           1-2-5-10        
6            |  | Steve             2           1-2-6                  
3            | Ina                  1           1-3               
7            |  | Aaron             2           1-3-7                     
11           |  |  | Gabriel        3           1-3-7-11           
9            |  |  | Rita           3           1-3-7-9            
13           |  |  |  | Michael     4           1-3-7-9-13       
14           |  |  |  | Didi        4           1-3-7-9-14         
*/  
--这个是移动之前的层次分布
IF OBJECT_ID('dbo.usp_movesubtree') IS NOT NULL
  DROP PROC dbo.usp_movesubtree;
GO
CREATE PROC dbo.usp_movesubtree
  @root  INT,--旧经理
  @mgrid INT--新经理
ASSET NOCOUNT ON;BEGIN TRAN;  UPDATE E
    SET lvl  = E.lvl + NM.lvl - OM.lvl,-- 级别=当前的级别+(新经理级别-原经理级别)
        path = STUFF(E.path, 1, LEN(OM.path), NM.path)
         -- 路径=自己的路径移除原来经理那部分再加上新的经理的那部分
         --这里的OM.path 是被替换经理的经理的路径比如旧经理是-3-7 那么OM.PATH 就是-3 
         --NM.path 是新经理的路径了比如要换新经理的EMPID是所以NM.path  就是1-2-5-10
         --所以如果本来旧经理一个手下比如说是-3-7-10 现在整个替换过来就是-2-5-10-7-10
  FROM dbo.Employees AS E          -- E = 员工
    JOIN dbo.Employees AS R        -- R = 根
      ON R.empid = @root
      AND E.path LIKE R.path + '%'
    JOIN dbo.Employees AS OM       -- OM = 旧经理
      ON OM.empid = R.mgrid
    JOIN dbo.Employees AS NM       -- NM = 新经理
      ON NM.empid = @mgrid;  -- 更新旧的经理的经理为新来的经理
  UPDATE dbo.Employees SET mgrid = @mgrid WHERE empid = @root;
  
COMMIT TRAN;
GO
BEGIN TRAN;  EXEC dbo.usp_movesubtree
  @root  = 7,
  @mgrid = 10;  -- After moving subtree
  SELECT empid, REPLICATE(' | ', lvl) + empname AS empname, lvl, path
  FROM dbo.Employees
  ORDER BY path;ROLLBACK TRAN; 
/*  
empid       empname                            lvl         path                    
 
----------- ------------------------------ ----------- ----------------------  
1           David                              0           1                        
2            | Eitan                           1           1-2                           
4            |  | Seraph                       2           1-2-4               
5            |  | Jiru                         2           1-2-5               
10           |  |  | Sean                      3           1-2-5-10           
7            |  |  |  | Aaron                  4           1-2-5-10-7         
11           |  |  |  |  | Gabriel             5           1-2-5-10-7-11     
9            |  |  |  |  | Rita                5           1-2-5-10-7-9             
12           |  |  |  |  |  | Emilia           6           1-2-5-10-7-9-12      
13           |  |  |  |  |  | Michael          6           1-2-5-10-7-9-13      
14           |  |  |  |  |  | Didi             6           1-2-5-10-7-9-14       
8            |  |  | Lilach                    3           1-2-5-8             
6            |  | Steve                        2           1-2-6    
3            | Ina                             1           1-3       
 
                                                             
*/  
--这个是移动之后大家注意观察和10 两位同志

Answer 4

/*
3.移除子树
就是把某个部门从公司取消掉
*/
--首先查看公司部门当前分布
SELECT empid, REPLICATE(' | ', lvl) + empname AS empname, lvl, path
FROM dbo.Employees
ORDER BY path;
/*  
empid       empname             lvl         path          
 
----------- -------------   -------------  -----------  
1           David                0           1            
2            | Eitan             1           1-2        
4            |  | Seraph         2           1-2-4          
5            |  | Jiru           2           1-2-5          
8            |  |  | Lilach      3           1-2-5-8      
6            |  | Steve          2           1-2-6           
3            | Ina               1           1-3                    
7            |  | Aaron          2           1-3-7        
11           |  |  | Gabriel     3           1-3-7-11    
9            |  |  | Rita        3           1-3-7-9               
12           |  |  |  | Emilia   4           1-3-7-9-12     
13           |  |  |  | Michael  4           1-3-7-9-13   
14           |  |  |  | Didi     4           1-3-7-9-14      
*/  --接着我们来开始一个部门比如移除Aaron手下的人
BEGIN TRAN;  DELETE FROM dbo.Employees
  WHERE path LIKE
    (SELECT M.path + '%'
     FROM dbo.Employees as M
     WHERE M.empid = 7);  --删除之后显示
  SELECT empid, REPLICATE(' | ', lvl) + empname AS empname, lvl, path
  FROM dbo.Employees
  ORDER BY path;ROLLBACK TRAN;
/*  
empid       empname               lvl         path                     
 
----------- ------------------ ----------- ------------  
1           David                 0           1                         
2            | Eitan              1           1-2                       
4            |  | Seraph          2           1-2-4        
5            |  | Jiru            2           1-2-5        
10           |  |  | Sean         3           1-2-5-10     
8            |  |  | Lilach       3           1-2-5-8      
6            |  | Steve           2           1-2-6    
3            | Ina                1           1-3  
*/  /*                                                                                                                                                                                                                  
4.查询
这里的查询可就轻松多啦~因为路径都有啦~
*/
--查询EMPID为的手下一批人
SELECT REPLICATE(' | ', E.lvl - M.lvl) + E.empname
FROM dbo.Employees AS E
  JOIN dbo.Employees AS M
    ON M.empid = 3 -- root
    AND E.path LIKE M.path + '%'
   --and  E.path LIKE M.path + '_%'--这里那个号就没了
   --and  E.lvl - M.lvl <= 2 --限制级数
   --WHERE NOT EXISTS        --自己本身不是经理的,返回的都是员工
      (SELECT *
           FROM dbo.Employees AS E2
           WHERE E2.mgrid = E.empid);
ORDER BY E.path;
/*  
------------------  
Ina  
 | Aaron  
 |  | Gabriel  
 |  | Rita  
 |  |  | Emilia  
 |  |  | Michael  
 |  |  | Didi  
 
*/