MySQL父级子级一个查询选择

本文介绍了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

其中父字段表示上层ID.例如，水果id是1，橙色是水果之一，因此父对象是1.

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.

但是我想做一个有效的MySQL查询来获取所有记录，格式为parent-> children-> parent-> children格式.我该怎么办?

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

推荐答案

您需要mysql不支持的递归联接.唯一可以做的就是确定最大深度(由于p-> c，所以我将其设置为1)，并以此确定所需的联接数:

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 :

最大深度=自连接数:

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

例如，如果最大深度为3，则需要3个自联接:

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