在MySQL中展平一棵树? [英] Flatten a tree in MySQL?

问题描述

我有一个称为Ancestry的表，该表由两列组成:父级和子级.我想使用表示两行沿袭的sql创建一个View或提取表:Parent和Dependent.受抚养人将是孩子，孙子，曾孙等. 例如，祖先表包含以下内容:

I have table, called Ancestry that consists of two columns: Parent and Child. I want to create a View or extract table using sql that represents lineage in two columns: Parent and Dependent. The Dependent will be a child, grandchild, great grandchild etc. For example, the Ancestry table contains the following:

Parent   Child
A  ------ A.1
A  ------ A.2
A.1 ----- A.1.1
A.1 ----- A.1.2
A.2 ----- A.2.1
A.2 ----- A.2.2  and so on

请注意，这些值仅是为了说明沿袭；实际值，子代数和树的深度完全灵活，我无法控制它们.

Note these values are purely to illustrate the lineage; actual values, number of children and depth of tree are totally flexible and I have no control over them.

我希望我的结果查询产生:

I want my result query to produce:

  Parent    Dependent
   null ---- A      (this row may not be possible!)
   A ------- A.1
   A ------- A.2
   A ------- A.1.1
   A ------- A.1.2
   A ------- A.2.1
   A ------- A.2.2
   A.1 ----- A.1.1
   A.1 ----- A.1.2
   A.2 ----- A.2.1
   A.2 ----- A.2.2

当我使用以下内容时，我也会得到同级，而不仅仅是直接的依赖项:

When I use the following I get siblings also, rather than just direct dependents:

select distinct  a.Parent,b.Child from Ancestry  a,Ancestry b
where  concat(a.Parent,b.Child not in (
select concat(a.Child,b.Child) from Ancestry a,Ancestry b
where a.Parent = b.Parent and a.Child <> b.Child)
order by a.Parent,b.Child;

我也很感谢其他替代方法.

I appreciate any alternative approaches also.

推荐答案

这可能有效

select p.parent, c.child
from       Ancestry  p
left join  Ancestry  c   on p.parent = left(c.child, len(p.parent))

这篇关于在MySQL中展平一棵树?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！