表结构id是主键,FatherId是父节点id,0代表根节点。Num是节点下边有多少个子节点
id FatherId Num
1 0 2
2 1 1
3 2 0
4 0 2
5 4 0
6 4 0加入我再添加一条父节点id是3和父节点id是6的记录,如何同时更新 id,1,2,3,4,6的Num ,其中id等于5的num不用更新得到如下结果
id FatherId Num
1 0 3
2 1 2
3 2 1
4 0 3
5 4 0
6 4 1
7 3 0
8 6 0
id FatherId Num
1 0 2
2 1 1
3 2 0
4 0 2
5 4 0
6 4 0加入我再添加一条父节点id是3和父节点id是6的记录,如何同时更新 id,1,2,3,4,6的Num ,其中id等于5的num不用更新得到如下结果
id FatherId Num
1 0 3
2 1 2
3 2 1
4 0 3
5 4 0
6 4 1
7 3 0
8 6 0
create table tb(id int, fatherid int, num int)
insert tb select 1, 0, 2
insert tb select 2, 1, 1
insert tb select 3, 2, 0
insert tb select 4, 0, 2
insert tb select 5, 4, 0
insert tb select 6, 4, 0--存储过程
create proc addnode(@id int, @fatherid int)
as
begin
--插入新数据
insert tb values(@id, @fatherid, 0) --列出所有父节点
;with cte as
(
select id,fatherid from tb where id = @fatherid
union all
select b.id,b.fatherid from cte a join tb b on a.fatherid = b.id
)
--修改所有父节点的num
update tb set num = num + 1 where id in (select id from cte)
end
goexec addnode 7,3
exec addnode 8,6select * from tb
/*
id fatherid num
----------- ----------- -----------
1 0 3
2 1 2
3 2 1
4 0 3
5 4 0
6 4 1
7 3 0
8 6 0(8 行受影响)
*/
楼主,记得结贴哦