MySQL-多级类别结构 [英] MySQL - Multi level category structure

问题描述

给出以下数据和表结构:

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

有了这个，我可以轻松获得给定类别的每个父级的ID.

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`

根据反馈，似乎很难获得所需格式的数据.

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

至少可以获取给定类别的所有子类别吗?因此，例如对于类别ID 1，它应在其下方输出三个类别(如果包含给定类别本身，则应输出四个).

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 */

这篇关于MySQL-多级类别结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！