无限树表的一个update语句无效，急，请教！

Question

最近在程序碰到一个问题，请教大家
实际情况比较复杂，大概意思如下
以下是一个递归表，用来做无限级树的id    name    parentid    [level]
1     a       null        0
2     b       1           1
3     c       2           2
4     d       2           2
5     e       2           2上面这个表里的数据会生成这样一个树
根
 └-a
   └-b
     ├-c
     ├-d
     └-e现在修改b这条记录使b这一级移动到根下，即变成下面这个样子
根
 ├-a
 └-b
   ├-c
   ├-d
   └-e当b这条记录修改完成后，数据库记录如下：id    name    parentid    [level]
1     a       null        0
2     b       null        0
3     c       2           2
4     d       2           2
5     e       2           2由于只修改了b这条记录，而这时c、d、e的level是不对的，需要更新c、d、e的level的值，这里我用如下sql语句来执行更新操作update t set [level] = (select [level] from t where id = parentid) + 1 where [level] = 2执行完毕后发现c、d、e的level都变成null了，但是正确的值应该是1才对，我又试了下面这句，也同样更新成nullupdate t set [level] = (select [level] from t where id = parentid) + 1 where (id = 3 or id = 4 or id = 5)这句sql为什么不能正确更新level值？要怎样改这句sql才能使它正确运行（运行后c、d、e的level应该变成1）多谢！

Answer 1

--复制指定节点及其所有子节点到指定结点的处理示例
CREATE TABLE tb(ID int,PID int,Name nvarchar(10))
INSERT tb SELECT 1,NULL,'山东省'
UNION ALL SELECT 2,1   ,'烟台市'
UNION ALL SELECT 4,2   ,'招远市'
UNION ALL SELECT 3,1   ,'青岛市'
UNION ALL SELECT 5,NULL,'四会市'
UNION ALL SELECT 6,5   ,'清远市'
UNION ALL SELECT 7,6   ,'小分市'
GO--节点复制处理函数
CREATE FUNCTION f_CopyNode(
@ID     int,      --复制此节点下的所有子节点
@PID   int,       --将@ID下的所有子节点复制到此节点下面
@NewID int=NULL   --新编码的开始值,如果指定为NULL,则为表中的最大编码+1
)RETURNS @t TABLE(OldID int,ID int,PID int)
AS
BEGIN
IF @NewID IS NULL
SELECT @NewID=COUNT(*)+1 FROM TB
DECLARE tb CURSOR LOCAL
FOR
SELECT ID FROM tb
WHERE PID=@ID
OPEN TB
FETCH tb INTO @ID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT @t VALUES(@ID,@NewID,@PID)
SET @NewID=@NewID+1
IF @@NESTLEVEL<32 --如果递归层数未超过32层(递归最大允许32层)
BEGIN
--递归查找当前节点的子节点
DECLARE @PID1 int
SET @PID1=@NewID-1
INSERT @t SELECT * FROM f_CopyNode(@ID,@PID1,@NewID)
SET @NewID=@NewID+@@ROWCOUNT  --排序号加上子节点个数
END
FETCH tb INTO @ID
END
RETURN
END
GO--调用函数将节点1下面的所有子节点复制到节点5下面
INSERT tb(ID,PID,Name)
SELECT a.ID,a.PID,b.Name
FROM f_CopyNode(1,5,DEFAULT) a,tb b
WHERE a.OldID=b.ID
SELECT * FROM tb
/*--结果
ID          PID         Name       
---------------- ----------------- ---------- 
1           NULL        山东省
2           1           烟台市
4           2           招远市
3           1           青岛市
5           NULL        四会市
6           5           清远市
7           6           小分市
8           5           烟台市
10          5           青岛市
9           8           招远市
--*/

Answer 2

--复制指定节点及其所有子节点到指定结点的处理示例(借鉴方式排序法)
CREATE TABLE tb(ID int,PID int,Name nvarchar(10))
INSERT tb SELECT 1,NULL,'山东省'
UNION ALL SELECT 2,1   ,'烟台市'
UNION ALL SELECT 4,2   ,'招远市'
UNION ALL SELECT 3,1   ,'青岛市'
UNION ALL SELECT 5,NULL,'四会市'
UNION ALL SELECT 6,5   ,'清远市'
UNION ALL SELECT 7,6   ,'小分市'
GO--节点复制处理函数
CREATE FUNCTION f_CopyNode(
@ID     int,        --复制此节点下的所有子节点
@PID    int,       --将@ID下的所有子节点复制到此节点下面
@NewID  int=NULL --新编码的开始值，如果指定为NULL，则为表中的最大编码+1
)RETURNS @t TABLE(OldID int,ID int,PID int,Level int)
AS
BEGIN
IF @NewID IS NULL
SELECT @NewID=COUNT(*) FROM TB
ELSE
SET @NewID=@NewID-1
DECLARE @Level int
SET @Level=1
INSERT @t(OldID,PID,Level) SELECT ID,@PID,@Level
FROM tb
WHERE PID=@ID
WHILE @@ROWCOUNT>0
BEGIN
UPDATE @t SET @NewID=@NewID+1,ID=@NewID
WHERE Level=@Level
SET @Level=@Level+1
INSERT @t(OldID,PID,Level) SELECT a.ID,b.ID,@Level
FROM tb a,@t b
WHERE a.PID=b.OldID
AND b.Level=@Level-1
END
RETURN
END
GO--调用函数将节点1下面的所有子节点复制到节点5下面
INSERT tb(ID,PID,Name)
SELECT a.ID,a.PID,b.Name
FROM f_CopyNode(1,5,DEFAULT) a,tb b
WHERE a.OldID=b.ID
SELECT * FROM tb
/*--结果
ID          PID         Name       
---------------- ----------------- ---------- 
1           NULL        山东省
2           1           烟台市
4           2           招远市
3           1           青岛市
5           NULL        四会市
6           5           清远市
7           6           小分市
8           5           烟台市
9           5           青岛市
10          8           招远市
--*/

Answer 3

update a
set [level] = (select [level] from t where id = a.parentid) + 1 
from t as a
where [level] = 2 

Answer 4

update a 
set [level] = (select [level] from t where id = a.parentid) + 1  
from t as a 
where [level] = 2  
条件不对

Answer 5

建议写个函数,取得该根节点下的所有子节点,
然后将这些子阶段的level统一减去1就可以了.

Answer 6

关于函数的话,可以参考2楼的f_CopyNode.

Answer 7

用 instead of 触发器

Answer 8

--> 测试数据: table_name
if object_id('table_name') is not null drop table table_name
create table table_name (id int,name varchar(1),parentid int,[level] int)
insert into table_name
select 1,'a',null,0 union all
select 2,'b',1,1 union all
select 3,'c',2,2 union all
select 4,'d',2,2 union all
select 5,'e',2,2
gocreate trigger trigger_name
on table_name
for update
as
set nocount on
if update(parentid)
begin
with T (add_level, id, name, parentid, [level]) as
(
select isnull(b.level+1,0)-a.[level], a.* from inserted as a left join table_name as b on isnull(a.parentid,0)=b.id
union all
select b.add_level, a.* from table_name as a join T as b on a.parentid=b.id
)
update a set a.level=a.level+b.add_level from table_name as a join T as b on a.id=b.id
end
set nocount off
gobegin tran
update table_name set parentid=null where id=2
select * from table_name
rollback tran
/*
id          name parentid    level
----------- ---- ----------- -----------
1           a    NULL        0
2           b    NULL        0
3           c    2           1
4           d    2           1
5           e    2           1
*/begin tran
update table_name set parentid=3 where id=4
select * from table_name
rollback tran
/*
id          name parentid    level
----------- ---- ----------- -----------
1           a    NULL        0
2           b    1           1
3           c    2           2
4           d    3           3
5           e    2           2
*/if object_id('table_name') is not null drop table table_name