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

一个树形结构表的设计
有张表,其中的数据之间的关系是树形的,树形结构信息包含在id字段中,

id字段也是这个表的pk

id
————
1
1.1
1.1.2
1.1.2.3
1.1.2.3.1
1.1.2.3.2
1.1.2.3.3
1.1.2.4
1.2
1.2.1
1.2.2
1.2.2.1

________________________

以上,1.1是1的下级id,1.1.2又是1.1的下级id.....

现在想往这个表中加入一种信息,目的是把id分为两类,主id和次级id

主id的下级id可能是主id也可能是次级id,次级id的下级id只能是次级id

比如上表,红色的是主id,黑色的是次级id,

1.1.2是主id,故其下属id既可以有次级id 1.1.2.3也可以有主id 1.1.2.4

1.2是次级id,故其下属所有id都是次级id

——————————————————

不知道我表达清楚没有。我的目的是想要方便快捷的计算出某个次级id的深度

这个深度指的是,当前次级id是第几层次级id,

比如:

1.1.2.3就是第1层次级id

1.1.2.3.1是第2层次级id

1.2是第1层次级id

1.2.2.1是第3层次级id

——————————————————

应该怎么设计?


------解决方案--------------------
SQL code
create table #EnterPrise
(
  Department nvarchar(50),--部门名称
  ParentDept nvarchar(50),--上级部门
  DepartManage nvarchar(30)--部门经理
)
insert into #EnterPrise select '技术部','总经办','Tom'
insert into #EnterPrise select '商务部','总经办','Jeffry'
insert into #EnterPrise select '商务一部','商务部','ViVi'
insert into #EnterPrise select '商务二部','商务部','Peter'
insert into #EnterPrise select '程序组','技术部','GiGi'
insert into #EnterPrise select '设计组','技术部','yoyo'
insert into #EnterPrise select '专项组','程序组','Yue'
insert into #EnterPrise select '总经办','','Boss'
--查询部门经理是Tom的下面的部门名称
;with hgo as
(
   select *,0 as rank from #EnterPrise where DepartManage='Tom'
   union all
   select h.*,h1.rank+1 from #EnterPrise h join hgo h1 on h.ParentDept=h1.Department
)
select * from hgo
/*
Department           ParentDept                DepartManage      rank
--------------- -------------------- ----------------------- -----------
技术部               总经办                    Tom               0
程序组               技术部                    GiGi              1
设计组               技术部                    yoyo              1
专项组               程序组                    Yue               2
*/
--查询部门经理是GiGi的上级部门名称
;with hgo as
(
   select *,0 as rank from #EnterPrise where DepartManage='GiGi'
   union all
   select h.*,h1.rank+1 from #EnterPrise h join hgo h1 on h.Department=h1.ParentDept
)
select * from hgo
/*
Department               ParentDept          DepartManage    rank
-------------------- ----------------------  -----------  -----------
程序组                   技术部                 GiGi           0
技术部                   总经办                 Tom            1
总经办                                          Boss           2
*/

------解决方案--------------------
SQL code
/*--树形数据处理方案   
    
  树形数据的排序,新增,修改,复制,删除,数据完整性检查,汇总统计   
  --邹建   2003.9--*/   
    
  /*--数据测试环境   
  表名tb,如果修改表名,则相应修改所有数据处理中涉及到的表名tb   
  id为编号(标识字段+主键)   
  pid为上级编号   
  name为名称,后面可以自行增加其他字段.   
    
  凡是未特殊标注的地方,对自行增加的字段不影响处理结果   
  --*/   
    
  create   table   tb(id   int   identity(1,1)   not   null   constraint   PK_tb   primary   key   clustered   
  ,pid   int,name   varchar(20))   
  insert   into   tb   
  select   0,'中国'   
  union   all   select   0,'美国'   
  union   all   select   0,'加拿大'   
  union   all   select   1,'北京'   
  union   all   select   1,'上海'   
  union   all   select   1,'江苏'   
  union   all   select   6,'苏州'   
  union   all   select   7,'常熟'   
  union   all   select   6,'南京'   
  union   all   select   6,'无锡'   
  union   all   select   2,'纽约'   
  union   all   select   2,'旧金山'   
  go   


/*--数据处理--*/   
    
  /*--   一个重要的函数,很多处理的地方都会用到   --*/   
  --自定义函数--获取编码累计   
  create   function   f_getmergid(@id   int)   
  returns   varchar(8000)   
  as   
  begin   
  declare   @re   varchar(8000),@pid   int   
    
  --为了数字排序正常,需要统一编码宽度   
  declare   @idlen   int,@idheader   varchar(20)   
  select   @idlen=max(len(id))   
  ,@idheader=space(@idlen)   
  from   tb   
    
  --得到编码累计   
  set   @re=right(@idheader+cast(