27,579
社区成员
发帖
与我相关
我的任务
分享
test
create table tb(PID int,EpsName varchar(10),ParentID int,EpsLevel int,Remark varchar(10))
insert into tb values(1,'XX总公司A' ,1,0,null)
insert into tb values(2,'XX总公司B' ,2,0,null)
insert into tb values(3,'北京分公司',1,1,null)
insert into tb values(4,'上海分公司',1,1,null)
insert into tb values(5,'天津分公司',1,1,null)
insert into tb values(6,'云南分公司',2,1,null)
insert into tb values(7,'广州分公司',2,1,null)
insert into tb values(8,'昆明办事处',6,2,null)
insert into tb values(9,'大理办事处',6,2,null)
go
--建立函数查找该PID下所有的子以及子对于PID的深度
create function f_cid(
@id int
)returns @re table(PID int,EpsLevel int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.PID,@l
from tb a inner join @re b on a.ParentID=b.PID
where b.EpsLevel = @l-1
and a.pid<>@id
end
return
end
go
declare @pid int
declare @parentid int
declare @EpsLevel int
-- 例如把8的parent改为2(@pid = 8,@parentid = 2)
set @pid = 8
set @parentid = 2
-- 查找parent的EpsLevel
select @EpsLevel = EpsLevel + 1
from tb
where pid = @parentid
-- update 前的数据
select * from tb
update a
set a.EpsLevel = @EpsLevel + b.EpsLevel
from tb a
inner join dbo.f_cid(@pid) b on a.pid = b.pid
-- update 后的数据
select * from tb
--可以直接查询某个节点的子节点和深度,下面得到节点1的所有子节点以及相对于节点1的深度
select * from dbo.f_cid(1)
--drop table tb
--drop function f_cid
create table os(pid int,epsname varchar(50),parentid int,epslevel int,remark varchar(50) default null)
insert into os select 1,'XX总公司A',1,0,null
insert into os select 2,'XX总公司B',2,0,null
insert into os select 3,'北京分公司',1,1,null
insert into os select 4,'上海分公司',1,1,null
insert into os select 5,'天津分公司',1,1,null
insert into os select 6,'云南分公司',2,1,null
insert into os select 7,'广州分公司',2,1,null
insert into os select 8,'昆明办事处',6,2,null
insert into os select 9,'大理办事处',6,2,null
--创建增加、修改触发器
create trigger tri_os on os for update
as
select *,cast(' ' as varchar(50)) fullpath into #os from os
DECLARE @i int,@j int
set @i=0
set @j=1
select @i=max(parentid) from #os
while @j<=@i
begin
update #os set fullpath=ltrim(#os.parentid)+','+a.fullpath
from #os inner join #os a on #os.parentid=a.pid
where #os.parentid=@j and #os.epslevel>0
set @j=@j+1
end
update os set epslevel=len(b.fullpath)-len(replace(b.fullpath,',','')) from os a,#os b
where a.pid=b.pid
go
--修改
update os set parentid=2 where pid=8
--查看结果:
select * from os