mysql根据子级查顶级,MySQL父级子级一个查询选择

I have a MySQL table with fields as below:

id name parent

1 Fruit 0

2 Meat 0

3 Orange 1

4 Beef 2

where parent field means the upper level id. For example Fruit id is 1, and Orange is one of the fruit so the parent is 1.

However I want to do an efficient MySQL query to fetch all records in the format parent->children->parent->children format. How can I do that?

The result record of the query should look like:

id name parent

1 Fruit 0

3 Orange 1

2 Meat 0

4 Beef 2

解决方案

You need a recursive join which mysql doesn't support. The only thing you can do is determine the maximum level of depth (i your case it 1 since you have p->c) and with this you can determine the number of joins needed :

maximum level of depth = number of self-joins:

SELECT

p.id as parent_id,

p.name as parent_id,

c1.id as child_id,

c1.name as child_name

FROM

my_table p

LEFT JOIN my_table c1

ON c1.parent = p.id

WHERE

p.parent=0

For example if you max level of depth was 3 the you would need 3 self-joins:

SELECT

p.id as parent_id,

p.name as parent_id,

c1.id as child_id_1,

c1.name as child_name_1,

c2.id as child_id_2,

c2.name as child_name_2,

c3.id as child_id_3,

c3.name as child_name_3

FROM

my_table p

LEFT JOIN my_table c1

ON c1.parent = p.id

LEFT JOIN my_table c2

ON c2.parent = c1.id

LEFT JOIN my_table c3

ON c3.parent = c2.id

WHERE

p.parent=0