从具有级别路径的表生成父子层次结构 [英] Generate a parent-child hierarchy from table with levels paths
问题描述
我设法在这样的表中转换了一些不可读的数据.SQL
表表示多父平面层次结构.问题是,如何生成正常的参差不齐的 sql parent-child
层次结构:ID |元素 |父
基于下面附加的级别和结构?
I managed to transform some unreadable data in such a table. The SQL
table represents a multipleparent flat hierarchy. The question is, how can I generate a normal ragged sql parent-child
hierarchy: ID | Element | Parent
based on the levels and the structure attached below ?
推荐答案
您可以创建一个具有层次结构和自动递增 ID 的新表,如下所示:
You could create a new table with the hierarchical structure, and an auto incrementing ID, like this:
create table hierarchy (
id int not null identity (1,1) primary key,
element varchar(100),
parent int
);
然后您首先要向其中添加 1 级元素,因为它们没有父元素:
Then you would first add the level 1 elements to it, as they have no parent:
insert into hierarchy (element, parent)
select distinct f.level1, null
from flat f;
由于您现在为这些元素生成了 id 值,您可以添加下一个级别,如下所示:
As you now have the id values generated for these elements, you can add the next level, like this:
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;
您可以将这种模式重复到下一个级别:
This pattern you can repeat to the next levels:
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;
...等,根据需要深入到各个级别.
... etc, as far into the levels as needed.
这篇关于从具有级别路径的表生成父子层次结构的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!