mysql 多层级,MySQL-多级类别结构

Given the following data and table structure:

id name parent_id

================================

1 food NULL

2 snacks 1

3 cakes 2

4 birthdays 3

I would like to output these rows alongside all of their parent categories. So for example I would like to generate a query to output the data as follows:

id name parent_id parent_1 parent_2 parent_3

===================================================================

1 food NULL NULL NULL NULL

2 snacks 1 1 NULL NULL

3 cakes 2 1 2 NULL

4 birthdays 3 1 2 3

With this, I can easily get the IDs of every parent level of a given category.

I have tried doing this using sub queries but not quite managed to get it right. This is what I have tried:

SELECT id, name, parent_id, parent_id AS _parent_1,

(SELECT parent_id FROM categories WHERE id = _parent_1) AS _parent_2,

(SELECT parent_id FROM categories WHERE id = _parent_2) AS _parent_3

FROM `categories`

EDIT: Based on the feedback, it seems it's going to be somewhat difficult to get the data in the desired format.

Would it at the very least be possible to get all child categories of a given category? So for example for category ID 1, it should output the three categories below it (or four if it will include the given category itself).

解决方案

You can simply self-join the table a number of times as required to meet maximum nesting depth. This could look like this:

SELECT

c.id AS id,

c.name AS name,

parent.id AS parent,

grandparent.id AS grandparent,

greatgrandparent.id AS greatgrandparent

/* add other columns based on max depth of nesting */

FROM categories AS c

LEFT JOIN categories AS parent

ON c.parent_id = parent.id

LEFT JOIN categories AS grandparent

ON parent.parent_id = grandparent.id

LEFT JOIN categories AS greatgrandparent

ON grandparent.parent_id = greatgrandparent.id

/* keep joining based on max depth of nesting */