日期:2014-05-18  浏览次数:20596 次

请教自连接表如何进行级联更新问题
需求描述:
有如下一张可以体现树视图层次的自连接表Eps,各字段及含义为:
字段名 类型 含义
---------------------------------------
PID Int 标识
EpsName Varchar(40) 公司名称
ParentID Int 上级ID(引用PID,如果是顶级则等于本级标识本身)
EpsLevel Tinyint 级别(0-顶级,1..n)
Remark Varchar(100) 备注

数据举例(活动数据):
PID EpsName ParentID EpsLevel Remark
----------------------------
1 XX总公司A 1 0 NULL
2 XX总公司B 2 0 NULL
3 北京分公司 1 1 NULL
4 上海分公司 1 1 NULL
5 天津分公司 1 1 NULL
6 云南分公司 2 1 NULL
7 广州分公司 2 1 NULL
8 昆明办事处 6 2 NULL
9 大理办事处 6 2 NULL
...........................................................
...........................................................
   
说明:该表通过ParentID与PID来表示上下层关系,通过DeptLevel表示级别(深度).

在实际应用中,如果改变了ParentID则EpsLevel会发生相应的变化(指向该PID的EpsLevel加1),要求自动计算并更新所有引用PID的EpsLevel的级别,并要求在数据库端SQL中实现.

请问能不能够在一条SQL语句中实现,或者有没有好的方法可以实现上述需求?


------解决方案--------------------
SQL code

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