执行update语句就行了. 如: update tb set parent_id=6 where id=14就能把14 从 5 下面移到 6 下面.
create table #tb (id int, Parent_id int, Position int, [Left] int, [right] int, [level] int, title nvarchar(100))insert #tb select 1,0,2,1,22,0,'ROOT' union all select 2,1,0,2,19,1,'C:' union all select 3,2,0,3,8,2,'_demo' union all select 4,3,0,4,5,3,'index.html' union all select 5,2,1,9,18,2,'_docs' union all select 6,1,1,20,21,1,'D:' union all select 12,3,1,6,7,3,'zmei.html' union all select 13,5,0,10,11,3,'A' union all -- select 14,5,3,16,17,3,'B' union all select 15,5,1,12,13,3,'C' union all select 16,5,2,14,15,3,'D'declare @id int declare @NewParent_id int set @NewParent_id=1 set @id=16 if exists(select 1 from #tb where id=@NewParent_id) and exists(select 1 from #tb where id=@id and title<>'ROOT') begin update s set s.Parent_id=@NewParent_id,s.[level]=t.[level]+1 from #tb as s join #tb as t on s.id=@id and t.id=@NewParent_id end select * from #tb--id Parent_id Position Left right level title ------------- ----------- ----------- ----------- ----------- ----------- ---------------------------------------------------------------------------------------------------- --1 0 2 1 22 0 ROOT --2 1 0 2 19 1 C: --3 2 0 3 8 2 _demo --4 3 0 4 5 3 index.html --5 2 1 9 18 2 _docs --6 1 1 20 21 1 D: --12 3 1 6 7 3 zmei.html --13 5 0 10 11 3 A --14 5 3 16 17 3 B --15 5 1 12 13 3 C --16 1 2 14 15 1 D --变了 -- --(11 row(s) affected)Position 、[Left] 、[right] 要处理的吗?
Position、left、right三字段步之表示什么,如果影响移动过程的操作,就需要考虑这些字段的必要性。
好的设计是好的算法的基础。
如:
update tb set parent_id=6 where id=14就能把14 从 5 下面移到 6 下面.
create table #tb
(id int,
Parent_id int,
Position int,
[Left] int,
[right] int,
[level] int,
title nvarchar(100))insert #tb
select 1,0,2,1,22,0,'ROOT' union all
select 2,1,0,2,19,1,'C:' union all
select 3,2,0,3,8,2,'_demo' union all
select 4,3,0,4,5,3,'index.html' union all
select 5,2,1,9,18,2,'_docs' union all
select 6,1,1,20,21,1,'D:' union all
select 12,3,1,6,7,3,'zmei.html' union all
select 13,5,0,10,11,3,'A' union all --
select 14,5,3,16,17,3,'B' union all
select 15,5,1,12,13,3,'C' union all
select 16,5,2,14,15,3,'D'declare @id int
declare @NewParent_id int
set @NewParent_id=1
set @id=16
if exists(select 1 from #tb where id=@NewParent_id) and exists(select 1 from #tb where id=@id and title<>'ROOT')
begin
update s set s.Parent_id=@NewParent_id,s.[level]=t.[level]+1 from #tb as s join #tb as t
on s.id=@id and t.id=@NewParent_id
end
select * from #tb--id Parent_id Position Left right level title
------------- ----------- ----------- ----------- ----------- ----------- ----------------------------------------------------------------------------------------------------
--1 0 2 1 22 0 ROOT
--2 1 0 2 19 1 C:
--3 2 0 3 8 2 _demo
--4 3 0 4 5 3 index.html
--5 2 1 9 18 2 _docs
--6 1 1 20 21 1 D:
--12 3 1 6 7 3 zmei.html
--13 5 0 10 11 3 A
--14 5 3 16 17 3 B
--15 5 1 12 13 3 C
--16 1 2 14 15 1 D --变了
--
--(11 row(s) affected)Position 、[Left] 、[right] 要处理的吗?