22,209
社区成员
发帖
与我相关
我的任务
分享
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
go
exec addnode 7,3
exec addnode 8,6
select * 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 行受影响)
*/