id pid path classid
1 0 0 1
2 1 1 2
3 2 2,1 3
4 3 3,2,1 4
5 0 0 1
6 4 4,3,2,1 5
7 6 6,4,3,2,1 6
8 7 7,6,4,3,2,1 7
path 父id
path 树的深度路径
classid 树的深度
想要结果
插入记录id 9 pid 8 获得path classid
修改记录id 6 的pid为2时 修改其path,classid和其子id的path,classid
删除记录id 3 把其子id都删掉
如果分不够 明天在加
------解决方案--------------------
少了维护classid
- SQL code
create trigger tr_tab_inserton tabfor insertasupdate a set path=cast(i.pid as varchar)+','+b.path, classid=b.classid+1from tab a,inserted i,tab bwhere a.id=i.idand b.id=i.pidgo
------解决方案--------------------
1直接插入就可以
2 ..
3delete tb where path like '%'+(select path from tb where id=3)
------解决方案--------------------
1,--insert
INSERT tt SELECT 9,8,(SELECT RTRIM(id) + ',' + path FROM tt WHERE ID=8),8
SELECT * FROM tt
2,要写函数或循环,分别针对目标pid已存在于路径中,和目标pid不存在于路径中.
3,比较简单.
路径法的树,查询容易,修改麻烦.
bom的树,查询麻烦,修改一般.
------解决方案--------------------
- SQL code
--建立测试环境create table BOM(id int,pid int,path varchar(20),classid int)insert into BOM values(1,0,rtrim('0 '),1)insert into BOM values(2,1,rtrim('1 '),2)insert into BOM values(3,2,rtrim('2,1 '),3)insert into BOM values(4,3,rtrim('3,2,1 '),4)insert into BOM values(5,0,rtrim('0 '),1)insert into BOM values(6,4,rtrim('4,3,2,1 '),5)insert into BOM values(7,6,rtrim('6,4,3,2,1 '),6)insert into BOM values(8,7,rtrim('7,6,4,3,2,1'),7)go--插入记录id 9 pid 8 获得path classid declare @ID int,@PID intset @ID=9set @PID=8insert into BOMselect @ID , @PID, isnull(rtrim(id),'')+isnull((case path when '0' then '' else ','+path end),'0'), isnull(classid,0)+1from BOMwhere [email protected] --select * from BOM--修改记录id 6 的pid为2时 修改其path,classid和其子id的path,classid set @ID =6set @PID=2update BOM set [email protected], path=(case @PID when 0 then '' else rtrim(@PID) end)+(select isnull(','+path,'') from BOM where [email protected])where [email protected]update BOMset path=left(path,charindex(','+rtrim(@ID)+',',','+path+',')-1)+rtrim(@ID)+(select isnull(','+path,'') from BOM where [email protected]) where charindex(','+rtrim(@ID)+',',','+path+',')>0--删除记录id 3 把其子id都删掉 set @ID=3delete BOM where [email protected] or charindex(','+rtrim(@ID)+',',','+path+',')>0--清除测试环境drop table BOM go