从具有级别路径的表中生成父-子层次结构。

Question

我设法在这样的表中转换了一些不可读的数据。SQL表表示多层平面层次结构。问题是，如何生成一个普通的、破旧的sql parent-child层次结构:基于下面所附的级别和结构的ID | Element | Parent？

​

​

Answer 1

您可以创建一个具有层次结构的新表和一个自动递增ID，如下所示：

create table hierarchy (
  id int not null identity (1,1) primary key,
  element varchar(100),
  parent int
);

然后，首先将第1级元素添加到其中，因为它们没有父元素：

insert into hierarchy (element, parent)
  select     distinct f.level1, null
  from       flat f;

由于您现在已经为这些元素生成了id值，所以可以添加下一个级别，如下所示：

insert into hierarchy (element, parent)
  select     distinct f.level2, h1.id
  from       hierarchy h1
  inner join flat f
          on f.level1 = h1.element
  where      h1.parent is null;

这种模式可以重复到下一个层次：

insert into hierarchy (element, parent)
  select     distinct f.level3, h2.id
  from       hierarchy h1
  inner join hierarchy h2
          on h2.parent = h1.id
  inner join flat f
          on f.level1 = h1.element
         and f.level2 = h2.element
  where      h1.parent is null;

insert into hierarchy (element, parent)
  select     distinct f.level4, h3.id
  from       hierarchy h1
  inner join hierarchy h2
          on h2.parent = h1.id
  inner join hierarchy h3
          on h3.parent = h2.id
  inner join flat f
          on f.level1 = h1.element
         and f.level2 = h2.element
         and f.level3 = h3.element
  where      h1.parent is null;

insert into hierarchy (element, parent)
  select     distinct f.level5, h3.id
  from       hierarchy h1
  inner join hierarchy h2
          on h2.parent = h1.id
  inner join hierarchy h3
          on h3.parent = h2.id
  inner join hierarchy h4
          on h4.parent = h3.id
  inner join flat f
          on f.level1 = h1.element
         and f.level2 = h2.element
         and f.level3 = h3.element
         and f.level4 = h4.element
  where      h1.parent is null;

insert into hierarchy (element, parent)
  select     distinct f.level6, h3.id
  from       hierarchy h1
  inner join hierarchy h2
          on h2.parent = h1.id
  inner join hierarchy h3
          on h3.parent = h2.id
  inner join hierarchy h4
          on h4.parent = h3.id
  inner join hierarchy h5
          on h5.parent = h4.id
  inner join flat f
          on f.level1 = h1.element
         and f.level2 = h2.element
         and f.level3 = h3.element
         and f.level4 = h4.element
         and f.level5 = h5.element
  where      h1.parent is null;

..。等等，尽可能深入到所需的水平。