mysql 展平各列,在MySQL中展平一棵树？

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.

解决方案

This may work

select p.parent, c.child

from Ancestry p

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