SQL Server 中树形表数据的处理总结
-- 使用函数的方法： --建立 演示环境 if object_id('tb_bookInfo') is not null drop table tb_bookInfo go create table tb_bookInfo(number int,name varchar(10),type int) insert tb_bookInfo select 1 ,'n1', 6 union all select 2 ,'n2', 3 if object_id('tb_bookType') is not null drop table tb_bookType go create table tb_bookType(id int,typeName varchar(10),parentid int) insert tb_bookType select 1,'英语',0 union all select 2,'生物',0 union all select 3,'计算机',0 union all select 4,'口语',1 union all select 5,'听力',1 union all select 6,'数据库',3 union all select 7,'软件工程',3 union all select 8,'SQL Server',6 select a.*,b.level from tb_bookInfo a,f_getC(3) b where a.type=b.id order by b.level /* number name type level ----------- ---------- ----------- ----------- 2 n2 3 0 1 n1 6 1 （所影响的行数为 2 行） */ --查所有父结点 if object_id('f_getP') is not null drop function f_getP go create function f_getP(@id int) returns @re table(id int,level 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.parentid,@l from tb_bookType a,@re b where a.id=b.id and b.level=@l-1 and a.parentid<>0 end update @re set level=@l-level return end go --查所有子结点 if object_id('f_getC') is not null drop function f_getC go create function f_getC(@id int) returns @re table(id int,level 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.id,@l from tb_bookType as a,@re as b where b.id=a.parentid and b.level=@l-1 end return end go --查所有父子结点 if object_id('f_getAll') is not null drop function f_getAll go create function f_getAll(@id int) returns @re table(id int,level 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.parentid,@l from tb_bookType a,@re b where a.id=b.id and b.level=@l-1 and a.parentid<>0 end update @re set level=@l-level while @@rowcount>0 begin set @l=@l+1 insert @re select a.id,@l from tb_bookType as a,@re as b where b.id=a.parentid and b.level=@l-1 end return end go --删除演示 drop table tb_bookInfo drop table tb_bookType drop function f_getP drop function f_getC drop function f_getAll GO --sqlserver2005的新方法 -- 建立演示环境 IF OBJECT_ID('[Dept]') IS NOT NULL DROP TABLE [Dept] GO CREATE TABLE Dept( id int PRIMARY KEY, parent_id int, name nvarchar(20)) INSERT Dept SELECT 1, 0, N'财务部' UNION ALL SELECT 2, 0, N'行政部' UNION ALL SELECT 3, 0, N'业务部' UNION ALL SELECT 4, 0, N'业务部' UNION ALL SELECT 5, 4, N'销售部' UNION ALL SELECT 6, 4, N'MIS' UNION ALL SELECT 7, 6, N'UI' UNION ALL SELECT 8, 6, N'软件开发' UNION ALL SELECT 9, 8, N'内部开发' GO --1、父-〉子 -- 查询指定部门下面的所有部门 DECLARE @Dept_name nvarchar(20) SET @Dept_name = N'MIS' ;WITH DEPTS AS( -- 定位点成员 SELECT * FROM Dept WHERE name = @Dept_name UNION ALL -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归 SELECT A.* FROM Dept A, DEPTS B WHERE A.parent_id = B.id ) SELECT * FROM DEPTS GO --结果如下 /* id parent_id name ----------- ----------- -------------------- 6 4 MIS 7 6 UI 8 6 软件开发 9 8 内部开发 （所影响的行数为 4 行） */ --2、子-〉父 -- 查询指定部门下面的所有部门 DECLARE @Dept_name nvarchar(20) SET @Dept_name = N'内部开发' ;WITH DEPTS AS( -- 定位点成员 SELECT * FROM Dept WHERE name = @Dept_name --SELECT d.id,d.parent_id,d.name,convert(nvarchar(50),d.name) as parent FROM Dept where @Dept_name UNION ALL -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归 SELECT a.* FROM Dept a, DEPTS b WHERE a.id = b.parent_id ) SELECT * FROM DEPTS GO --结果如下 /* id parent_id name ----------- ----------- -------------------- 9 8 内部开发 8 6 软件开发 6 4 MIS 4 0 业务部 （所影响的行数为 4 行） */ -- 删除演示环境 DROP TABLE Dept -- 一个不错的例子 if object_id('[tb]') is not null drop table [tb] go create table [tb]([modeid] int,modename varchar(20),parentid int) insert [tb] select 100 ,'商品管理', 0 union all select 101 ,'定单管理', 0 union all select 102 ,'用户管理', 0 union all select 104 ,'学院广告', 0 union all select 105 ,'系统设置', 0 union all select 106 ,'附件管理', 0 union all select 107 ,'商品管理', 100 union all select 108 ,'明细管理', 100 union all select 109 ,'物流管理', 100 union all select 110 ,'商品信息管理', 107 union all select 111 ,'商品分类管理', 107 union all select 112 ,'回收站管理', 107 union all select 114 ,'团购管理', 108 union all select 115 ,'拍卖管理', 108 union all select 116 ,'优惠管理', 108 union all select 117 ,'会员管理', 102 union all select 118 ,'会员卡管理', 102 union all select 119 ,'资金管理', 102 union all select 120 ,'管理员管理', 102 union all select 121 ,'添加管理员', 120 union all select 122 ,'修改管理员', 120 go --查所有子结点 if object_id('f_getC') is not null drop function f_getC go create function f_getC(@id int) returns @re table(id int,level int,sort varchar(10)) as begin declare @l int set @l=0 insert @re select @id,@l,null while @@rowcount>0 begin set @l=@l+1 insert @re select a.modeid,@l,ltrim(isnull(b.sort,a.modeid)) from tb as a,@re as b where b.id=a.parentid and b.level=@l-1 end update @re set level = level -1 return end go select a.modeid,a.parentid,REPLICATE(' ',b.level) +'┝'+a.modename,b.level,b.sort from tb a,f_getC(0) b where a.modeid=b.id order by case when b.level<2 then 0 else 1 end,b.sort,b.level /* modeid parentid sort level ----------- ----------- -------------------------------------------------- ---------- ----------- 100 0 ┝商品管理 100 0 107 100 ┝商品管理 100 1 108 100 ┝明细管理 100 1 109 100 ┝物流管理 100 1 101 0 ┝定单管理 101 0 102 0 ┝用户管理 102 0 117 102 ┝会员管理 102 1 118 102 ┝会员卡管理 102 1 119 102 ┝资金管理 102 1 120 102 ┝管理员管理 102 1 104 0 ┝学院广告 104 0 105 0 ┝系统设置 105 0 106 0 ┝附件管理 106 0 110 107 ┝商品信息管理 100 2 111 107 ┝商品分类管理 100 2 112 107 ┝回收站管理 100 2 114 108 ┝团购管理 100 2 115 108 ┝拍卖管理 100 2 116 108 ┝优惠管理 100 2 121 120 ┝添加管理员 102 2 122 120 ┝修改管理员 102 2 （所影响的行数为 21 行） */ --查所有子结点,带路径与排序 if object_id('f_getC') is not null drop function f_getC go create function f_getC(@id int) returns @re table(id int,level int,sort varchar(100),path varchar(500)) as begin declare @l int set @l=0 insert @re select [modeid],@l,right('00000'+ltrim(modeid),5),modename from tb where parentid=@id while @@rowcount>0 begin set @l=@l+1 insert @re select a.modeid,@l,b.sort+right('00000'+ltrim(a.modeid),5), b.path+' - '+a.modename from tb as a,@re as b where b.id=a.parentid and b.level=@l-1 end update @re set level = level return end go select a.modeid,a.parentid,REPLICATE(' ',b.level) +'┝'+a.modename,b.level,b.sort ,b.path from tb a,f_getC(0) b where a.modeid=b.id order by sort /* modeid parentid level ----------- ----------- -------------------- ----------- -------------------- ---------------------------------------- 100 0 ┝商品管理 0 00100 商品管理 107 100 ┝商品管理 1 0010000107 商品管理 - 商品管理 110 107 ┝商品信息管理 2 001000010700110 商品管理 - 商品管理 - 商品信息管理 111 107 ┝商品分类管理 2 001000010700111 商品管理 - 商品管理 - 商品分类管理 112 107 ┝回收站管理 2 001000010700112 商品管理 - 商品管理 - 回收站管理 108 100 ┝明细管理 1 0010000108 商品管理 - 明细管理 114 108 ┝团购管理 2 001000010800114 商品管理 - 明细管理 - 团购管理 115 108 ┝拍卖管理 2 001000010800115 商品管理 - 明细管理 - 拍卖管理 116 108 ┝优惠管理 2 001000010800116 商品管理 - 明细管理 - 优惠管理 109 100 ┝物流管理 1 0010000109 商品管理 - 物流管理 101 0 ┝定单管理 0 00101 定单管理 102 0 ┝用户管理 0 00102 用户管理 117 102 ┝会员管理 1 0010200117 用户管理 - 会员管理 118 102 ┝会员卡管理 1 0010200118 用户管理 - 会员卡管理 119 102 ┝资金管理 1 0010200119 用户管理 - 资金管理 120 102 ┝管理员管理 1 0010200120 用户管理 - 管理员管理 121 120 ┝添加管理员 2 001020012000121 用户管理 - 管理员管理 - 添加管理员 122 120 ┝修改管理员 2 001020012000122 用户管理 - 管理员管理 - 修改管理员 104 0 ┝学院广告 0 00104 学院广告 105 0 ┝系统设置 0 00105 系统设置 106 0 ┝附件管理 0 00106 附件管理 (21 行受影响) */ ---------- --删除节点 --建立测试环境 IF OBJECT_ID('GoodType') IS NOT NULL DROP TABLE GoodType GO CREATE TABLE GoodType ( id int , name varchar(20), pid int, tree int ) GO INSERT GoodType select '1','a','0','0' union all select '2','aa','1','1' union all select '3','ab','1','1' union all select '4','aaa','2','2' union all select '5','aba','3','2' union all select '6','abaa','5','3' union all select '7','abab','5','3' go create trigger trd_GoodType on GoodType instead of delete as begin update t set t.pid=d.pid ,t.tree=t.tree-1 from GoodType t,deleted d where t.pid=d.id delete GoodType where id in( select id from deleted) end go create trigger tru_GoodType on GoodType for update as begin update t set t.tree=t.tree-1 from GoodType t,inserted i,deleted d where t.pid=i.id and t.pid=d.id and i.tree=d.tree-1 end go --查询 delete GoodType where id=3 select * from GoodType go --结果 /* (7 行受影响) (2 行受影响) (1 行受影响) (1 行受影响) (1 行受影响) id name pid tree ----------- -------------------- ----------- ----------- 1 a 0 0 2 aa 1 1 4 aaa 2 2 5 aba 1 1 6 abaa 5 2 7 abab 5 2 (6 行受影响) */
帮助链接：http://technet.microsoft.com/zh-cn/library/ms175972.aspx
