一个树形结构表的设计

[josxhn]

有张表，其中的数据之间的关系是树形的，树形结构信息包含在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？

[ws_hgo]

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
*/

[ai_li7758521]

建议：TB(ID,Name,ISMain,ParentID)
ID，另行构造，Name值取你现在的ID值，ISMain标志是否为主ID，ParentID记录该数据的父记录中的ID。

[ai_li7758521]

/*--树形数据处理方案   
    
  树形数据的排序,新增,修改,复制,删除,数据完整性检查,汇总统计   
  --邹建   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(@id   as   varchar),@idlen)   
  select   @pid=pid   from   tb   where   id=@id   
  while   @@rowcount>0   
  select   @re=right(@idheader+cast(@pid   as   varchar),@idlen)+','+@re   
  ,@pid=pid   from   tb   where   id=@pid   
  return(@re)   
  end   
  go   




/*--数据显示排序--*/   
  --分级显示--横向,先一级,后二级...   
  select   *   from   tb   order   by   pid   
    
  --分级显示--纵向   
  select   *   from   tb   order   by   dbo.f_getmergid(id)   
  go   
    
  /*--数据统计--*/   
  --分级统计,每个地区下的明细地区数   
  select   *,   
  明细地区数=(select   count(*)   from   tb   where   dbo.f_getmergid(id)   like   dbo.f_getmergid(a.id)+',%')   
  from   tb   a   order   by   dbo.f_getmergid(id)   
    
  go   
  /*--数据新增,修改   
    
  数据新增,修改(包括修改所属的类别)没有什么技巧   
  ,只需要检查所属的上级是否存在就行了.这个可以简单的用下面的语句来解决:   
  if   exists(select   1   from   tb   where   id=@id)   print   '存在'   else   print   '不存在'   
  --*/   
    
  /*--数据删除--*/   
  create   proc   p_delete   
  @id   int, --要删除的id   
  @deletechild   bit=0 --是否删除子   1.删除子,0.如果@id有子,则删除失败.   
  as   
  if   @deletechild=1   
  delete   from   tb   where   dbo.f_getmergid(id)   like   dbo.f_getmergid(@id)+'%'   
  else   
  if   exists(select   1   from   tb   where   pid=@id)   
  goto   lbErr   
  else   
  delete   from   tb   where   id=@id   
    
  return lbErr:   
  RAISERROR   ('该结点下有子结点,不能删除',   16,   1)   
  go   
    
  --调用示例   
  --删除'美国'的数据   
  --exec   p_delete   2 --不包含子,因为有美国下有子,所以删除会出错   
  exec   p_delete   2,1 --包含子,将删除美国及所有数据   
  go   







 /*--数据完整性检查--*/   
  --自定义函数--检测某个编码出发,是否被循环引用   
  create   function   f_chkid(@id   int)   
  returns   bit --循环,返回1,否则返回0   
  as   
  begin   
  declare   @re   bit,@pid   int   
    
  set   @re=0   
    
  --检测   
  select   @pid=pid   from   tb   where   id=@id   
  while   @@rowcount>0   
  begin   
  if   @pid=@id   
  begin   
  set   @re=1   
  goto   lbErr   
  end   
  select   @pid=pid   from   tb   where   id=@pid   
  end   
    
  lbErr:   
  return(@re)   
  end   
  go   
    
  --显示表中的那些数据不符合规范   
  select   *   from   tb   a     
  where   not   exists(select   1   from   tb   where   id=a.pid)   
  or   dbo.f_chkid(id)=1   
  go   
    
  /*--数据复制   
    
  如果表中包含自定义字段,需要修改存储过程   
  存在嵌套不超过32层的问题.   
  --*/   
    
  --创建复制的存储过程--复制指定结点下的子结点到另一个结点下   
  create   proc   p_copy   
  @s_id   int, --复制该项下的所有子项   
  @d_id   int, --复制到此项下   
  @new_id   int --新增加项的开始编号   
  as   
  declare   @nid   int,@oid   int,@name   varchar(20)   
  select   id,name   into   #temp   from   tb   where   pid=@s_id   and   id<@new_id   
  while   exists(select   1   from   #temp)   
  begin   
  select   @oid=id,@name=name   from   #temp   
  insert   into   tb   values(@d_id,@name)   
  set   @nid=@@identity   
  exec   p_copy   @oid,@nid,@new_id   
  delete   from   #temp   where   id=@oid   
  end   
  go   
    
  --创建批量复制的存储过程--复制指定结点及其下面的所有子结点,并生成新结点   
  create   proc   p_copystr   
  @s_id   varchar(8000) --要复制项的列表,用逗号分隔   
  as   
  declare   @nid   int,@oid   int,@name   varchar(20)   
  set   @s_id=','+@s_id+','   
  select   id,name   into   #temp   from   tb   
  where   charindex(','+cast(id   as   varchar)+',',   @s_id)>0   
  while   exists(select   1   from   #temp)   
  begin   
  select   @oid=id,@name=name   from   #temp   
  insert   into   tb   values(@oid,@name)   
  set   @nid=@@identity   
  exec   p_copy   @oid,@nid,@nid   
  delete   from   #temp   where   id=@oid   
  end   
  go   
    
  --测试   
  exec   p_copystr   '5,6'   
    
  --显示处理结果   
  select   *   from   tb   order   by   dbo.f_getmergid(id)   
    
  go   
    
  --删除数据测试环境   
  drop   table   tb   
  drop   function   f_getmergid,f_chkid   
  drop   proc   p_delete,p_copystr,p_copy







/*--数据完整性检查--*/   
  --自定义函数--检测某个编码出发,是否被循环引用   
  create   function   f_chkid(@id   int)   
  returns   bit --循环,返回1,否则返回0   
  as   
  begin   
  declare   @re   bit,@pid   int   
    
  set   @re=0   
    
  --检测   
  select   @pid=pid   from   tb   where   id=@id   
  while   @@rowcount>0   
  begin   
  if   @pid=@id   
  begin   
  set   @re=1   
  goto   lbErr   
  end   
  select   @pid=pid   from   tb   where   id=@pid   
  end   
    
  lbErr:   
  return(@re)   
  end   
  go   
    
  --显示表中的那些数据不符合规范   
  select   *   from   tb   a     
  where   not   exists(select   1   from   tb   where   id=a.pid)   
  or   dbo.f_chkid(id)=1   
  go   
    
  /*--数据复制   
    
  如果表中包含自定义字段,需要修改存储过程   
  存在嵌套不超过32层的问题.   
  --*/   
    
  --创建复制的存储过程--复制指定结点下的子结点到另一个结点下   
  create   proc   p_copy   
  @s_id   int, --复制该项下的所有子项   
  @d_id   int, --复制到此项下   
  @new_id   int --新增加项的开始编号   
  as   
  declare   @nid   int,@oid   int,@name   varchar(20)   
  select   id,name   into   #temp   from   tb   where   pid=@s_id   and   id<@new_id   
  while   exists(select   1   from   #temp)   
  begin   
  select   @oid=id,@name=name   from   #temp   
  insert   into   tb   values(@d_id,@name)   
  set   @nid=@@identity   
  exec   p_copy   @oid,@nid,@new_id   
  delete   from   #temp   where   id=@oid   
  end   
  go   
    
  --创建批量复制的存储过程--复制指定结点及其下面的所有子结点,并生成新结点   
  create   proc   p_copystr   
  @s_id   varchar(8000) --要复制项的列表,用逗号分隔   
  as   
  declare   @nid   int,@oid   int,@name   varchar(20)   
  set   @s_id=','+@s_id+','   
  select   id,name   into   #temp   from   tb   
  where   charindex(','+cast(id   as   varchar)+',',   @s_id)>0   
  while   exists(select   1   from   #temp)   
  begin   
  select   @oid=id,@name=name   from   #temp   
  insert   into   tb   values(@oid,@name)   
  set   @nid=@@identity   
  exec   p_copy   @oid,@nid,@nid   
  delete   from   #temp   where   id=@oid   
  end   
  go   
    
  --测试   
  exec   p_copystr   '5,6'   
    
  --显示处理结果   
  select   *   from   tb   order   by   dbo.f_getmergid(id)   
    
  go   
    
  --删除数据测试环境   
  drop   table   tb   
  drop   function   f_getmergid,f_chkid   
  drop   proc   p_delete,p_copystr,p_copy




增加几个函数:   
    
    
  /*--   得到级别   --*/   
  create   function   f_getidlevel(@id   int)   
  returns   int   
  as   
  begin   
  declare   @re   int,@pid   int   
  set   @re=1   
    
  --得到级别(深度)   
  select   @pid=pid   from   tb   where   id=@id   
  while   @@rowcount>0   
  select   @re=@re+1   
  ,@pid=pid   from   tb   where   id=@pid   
  return(@re)   
  end   
  go   
    
  --调用   
  select   *,dbo.f_getidlevel(id)   from   tb



/*--   得到指定id的子id列表   --*/   
  --不包含排序字段的情况   
  create   function   f_getchildid(@id   int)   
  returns   @re   table(id   int)   
  as   
  begin   
  insert   into   @re   select   id   from   tb   where   pid=@id   
  while   @@rowcount>0   
  insert   into   @re select   a.id     
  from   tb   a   inner   join   @re   b   on   a.pid=b.id   
  where   a.id   not   in(select   id   from   @re)   
  return   
  end   
  go   
    
  --包含排序字段的情况   
  create   function   f_getchildidsort(@id   int)   
  returns   @re   table(id   int,sortid   varchar(8000))   
  as   
  begin   
  --为了数字排序正常,需要统一编码宽度   
  declare   @idlen   int,@idheader   varchar(20)   
  select   @idlen=max(len(id))   
  ,@idheader=space(@idlen)   
  from   tb   
    
  insert   into   @re   select   id,right(@idheader+cast(id   as   varchar),@idlen)   
  from   tb   where   pid=@id   
  while   @@rowcount>0   
  insert   into   @re select   a.id,right(@idheader+cast(a.id   as   varchar),@idlen)+','+b.sortid     
  from   tb   a   inner   join   @re   b   on   a.pid=b.id   
  where   a.id   not   in(select   id   from   @re)   
  return   
  end   
  go   
    
  --调用示例,显示1的所有子.   
  select   a.*   from   tb   a   inner   join   dbo.f_getchildidsort(1)   b   on   a.id=b.id   order   by   b.sortid   
  


/*--   得到指定id的父id列表   --*/   
  --不包含排序字段的情况   
  create   function   f_getparentid(@id   int)   
  returns   @re   table(id   int)   
  as   
  begin   
  declare   @pid   int   
  select   @pid=pid   from   tb   where   id=@id   
  while   @pid<>0   
  begin   
  insert   into   @re   values(@pid)   
  select   @pid=pid   from   tb   where   id=@pid   
  end   
  return   
  end   
  go   

[changjiangzhibin]

何不加一字段直接标明是否主ID

[josxhn]

1楼：想要设计，怎样在原表的基础上做一些改动来实现需求

2楼：谢谢你贴的代码，不过貌似和我说的需求不是一回事

[ws_hgo]

楼主人了
出来说个话...

[josxhn]

[Quote=引用 5 楼 changjiangzhibin 的回复:]

何不加一字段直接标明是否主ID
[/Quote]

我需要计算次级ID的深度，

仅仅标注主ID的话，对于每个次级ID判断深度的时候都要逐层向上判断，很麻烦

[josxhn]

[Quote=引用 7 楼 ws_hgo 的回复:]

楼主人了
出来说个话...
[/Quote]

出来鸟

[ws_hgo]

[Quote=引用 6 楼 josxhn 的回复:]

1楼：想要设计，怎样在原表的基础上做一些改动来实现需求

2楼：谢谢你贴的代码，不过貌似和我说的需求不是一回事
[/Quote]

要做你的原表上面的话
基本上不行的
你的这个表要重新设计的

create table tb
(
  ID int,
  ParentID int--父类ID
)

[josxhn]

[Quote=引用 9 楼 ws_hgo 的回复:]

引用 6 楼 josxhn 的回复:

1楼：想要设计，怎样在原表的基础上做一些改动来实现需求

2楼：谢谢你贴的代码，不过貌似和我说的需求不是一回事


SQL code
要做你的原表上面的话
基本上不行的
你的这个表要重新设计的

create table tb
(
ID int,
ParentID int--父类ID
)
[/Quote]

明白你的意思

我原来的表也是可以的，比如对1.2标记为主id而1.2.3为次级id的话，对于1.2.3.4.5.6向上判断5次即可得到深度

你这样设计的话，应该也是要向上判断5次，

我们的做法只是实现的sql的语句不同，你绝的我说的对吗

[josxhn]

我这种设计做子树的迁移会很麻烦，但是直接定位某个节点会比较简单

你那样的话对子树迁移的处理应该是很方便的

这个应该是主要区别吧

不过好像对目前这个需求影响不大

[abuying]

如果是server 2008，就有个支持树形结构的数据类型。

[josxhn]

[Quote=引用 13 楼 abuying 的回复:]

如果是server 2008，就有个支持树形结构的数据类型。
[/Quote]

2005.。。

[abuying]

２００８有个hierarchyid 数据类型

hierarchyid 数据类型是一种长度可变的系统数据类型。可使用 hierarchyid 表示层次结构中的位置。类型为 hierarchyid 的列不会自动表示树。由应用程序来生成和分配 hierarchyid 值，使行与行之间的所需关系反映在这些值中。

hierarchyid 数据类型的值表示树层次结构中的位置。hierarchyid 的值具有以下属性：

非常紧凑

在具有 n 个节点的树中，表示一个节点所需的平均位数取决于平均端数（节点的平均子级数）。端数较小时 (0-7)，大小约为 6*logAn 位，其中 A 是平均端数。对于平均端数为 6 级、包含 100,000 个人的组织层次结构，一个节点大约占 38 位。存储时，此值向上舍入为 40 位，即 5 字节。


按深度优先顺序进行比较

给定两个 hierarchyid 值 a 和 b，a<b 表示在对树进行深度优先遍历时，先找到 a，后找到 b。hierarchyid 数据类型的索引按深度优先顺序排序，在深度优先遍历中相邻的节点的存储位置也相邻。例如，一条记录的子级的存储位置与该记录的存储位置是相邻的。有关详细信息，请参阅使用 hierarchyid 数据类型（数据库引擎）。


支持任意插入和删除

使用 GetDescendant 方法，始终可以在任意给定节点的右侧、左侧或任意两个同级节点之间生成同级节点。在层次结构中插入或删除任意数目的节点时，该比较属性保持不变。大多数插入和删除操作都保留了紧凑性属性。但是，对于在两个节点之间执行的插入操作，所产生的 hierarchyid 值的表示形式在紧凑性方面将稍微降低。


hierarchyid 类型中所用的编码限制为 892 字节。因此，如果节点的表示形式中包含过多级别，以至于 892 字节不足以容纳它，则该节点不能用 hierarchyid 类型表示。


hierarchyid 类型与 SqlHierarchyId 数据类型一样，可供 CLR 客户端使用。

备注
hierarchyid 类型对层次结构树中有关单个节点的信息进行逻辑编码的方法是：对从树的根目录到该节点的路径进行编码。这种路径在逻辑上表示为一个在根之后被访问的所有子级的节点标签序列。表示形式以一条斜杠开头，只访问根的路径由单条斜杠表示。对于根以下的各级，各标签编码为由点分隔的整数序列。子级之间的比较就是按字典顺序比较由点分隔的整数序列。每个级别后面紧跟着一个斜杠。因此斜杠将父级与其子级分隔开。例如，以下是长度分别为 1 级、2 级、2 级、3 级和 3 级的有效 hierarchyid 路径：

/


/1/


/0.3.-7/


/1/3/


/0.1/0.2/


可在任何位置插入节点。插入在 /1/2/ 之后、/1/3/ 之前的节点可表示为 /1/2.5/。插入在 0 之前的节点的逻辑表示形式为一个负数。例如，/1/1/ 之前的节点可表示为 /1/-1/。节点不能有前导零。例如，/1/1.1/ 有效，但 /1/1.01/ 无效。为了避免错误，请使用 GetDescendant 方法插入节点。

数据类型转换
hierarchyid 数据类型可按如下方法转换为其他数据类型：

使用 ToString() 方法将 hierarchyid 值转换为作为 nvarchar(4000) 数据类型的逻辑表示形式。


使用 Read () 和 Write () 将 hierarchyid 转换为 varbinary。


不支持将 hierarchyid 转换为 XML。若要通过 SOAP 传输 hierarchyid 参数，首先应将它们转换为字符串。对含有 hierarchyid 的表执行含 FOR XML 子句的查询将失败，除非先将该列转换为字符数据类型

[josxhn]

[Quote=引用 15 楼 abuying 的回复:]

２００８有个hierarchyid 数据类型

hierarchyid 数据类型是一种长度可变的系统数据类型。可使用 hierarchyid 表示层次结构中的位置。类型为 hierarchyid 的列不会自动表示树。由应用程序来生成和分配 hierarchyid 值，使行与行之间的所需关系反映在这些值中。

hierarchyid 数据类型的值表示树层次结构中的位置。hierarchyi……
[/Quote]

恩，挺有用的，谢谢啊

下次有机会再给分你

[微笑0081]

[Quote=

引用 15 楼 的回复:]
２００８有个hierarchyid 数据类型

hierarchyid 数据类型是一种长度可变的系统数据类型。可使用 hierarchyid 表示层次结构中的位置。类型为 hierarchyid 的列不会自动表示树。由应用程序来生成和分配 hierarchyid 值，使行与行之间的所需关系反映在这些值中。

hierarchyid 数据类型的值表示树层次结构中的位置。hierarchyid……
[/Quote]123132