需求描述:
有如下一张可以体现树视图层次的自连接表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)asbegindeclare @l intset @l=0insert @re select @id,@lwhile @@rowcount>0beginset @[email protected]+1insert @re select a.PID,@lfrom tb a inner join @re b on a.ParentID=b.PIDwhere b.EpsLevel = @l-1 and a.pid<>@idendreturnendgodeclare @pid intdeclare @parentid intdeclare @EpsLevel int-- 例如把8的parent改为2(@pid = 8,@parentid = 2)set @pid = 8set @parentid = 2-- 查找parent的EpsLevelselect @EpsLevel = EpsLevel + 1from tb where pid = @parentid-- update 前的数据select * from tbupdate a set a.EpsLevel = @EpsLevel + b.EpsLevelfrom tb ainner 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