mysql将父子级字段拼接_MySQL所有父子关系

I have a table named table. It has a field named id with type INT(11) that stands for an identifier of the row, it has other fields but I don't think they are relevant for this problem.

I have another table named table_children. It has a field named parent with type INT(11) that refers to table.id as a foreign key. It has another field named child with type INT(11) that also refers to table.id as a foreign key. This table describes table row to table row parent-child relationships.

Here is a probable setting.

table table_children

id parent child

0 0 1

1 1 2

2 1 3

3 3 4

4

How can I get the id's of all the descendents of 0 in a minimum number of requests? The answer here would be 1, 2, 3, 4.

Thank you for your help.

解决方案

With MySQL, the easiest way I do this is to store all paths in the tree, creating a transitive closure.

table_children

parent child

0 0

1 1

2 2

3 3

4 4

0 1

0 2

0 3

0 4

1 2

1 3

1 4

3 4

Now you can query it thus:

SELECT t.*

FROM table_children c

JOIN table t ON c.child = t.id

WHERE c.parent = 0;

See also: