如何编写一个MYSQL查询，将返回的孩子嵌套在父母下？ [英] How to write a MYSQL query that will return children nested under parents?

问题描述

我不知道我要问的是否可能，但这里是我的情况。我的表结构有点像这样：

I don't know if what I'm asking is even possible, but here's my situation. I have a table structured somewhat like this:

+--------------------------------------------------+
|   id   |   parent_id   |   name   |   category   | ....
+--------------------------------------------------+
|    0   |       -1      |   item0  |      1       |
|    1   |        0      |   item1  |      1       |
|    2   |        0      |   item2  |      1       |
|    3   |        2      |   item3  |      1       | 
|    4   |        2      |   item4  |      1       | 
|    5   |       -1      |   item5  |      1       | 
+--------------------------------------------------+

parent_id为-1表示它是一个没有父母。每个项目将有更多的信息列。我需要以某种方式输出类别中嵌套的所有项目，如下所示：

A parent_id of -1 will mean it is a "base" item with no parent. Each item will have more columns of information. I need to somehow output all items in a category nested like the following:

item0 => item1    
      => item2
            => item3
            => item4  
item5  

我不知道这是否有意义，但希望它do！

I don't know if that makes sense or not, but hopefully it does!

我可以想到这样做的唯一方法是进行查询以获取所有的base项（查询parent_id = -1的行）遍历每个结果行，查询其parent_id等于当前行的id的行，然后重复该过程越来越深，直到基本项目没有任何子元素。

The only way I can think of doing this is making a query to get all of the "base" items (querying for rows with parent_id = -1) then iterate through every resulting row, querying for rows that have their parent_id equal to the current row's id, then repeating the process going deeper and deeper until there aren't any more children for a base item.

有更好的方法吗？

谢谢!!

推荐答案

在纯SQL中是不可能的。

It is not possible in pure SQL.

SQL旨在处理关系数据而不是树（分层数据）。

SQL is intended to work with relational data not trees (hierarchical data).

一个SQL模式，但是你不能像你想要的那样产生一个树。

You can represent tree in an SQL schema, however you won't be a able to result in a tree as you intend to do.

唯一的方法是获得一个可用的结果制作与您存储的级别一样多的连接。

The only way to do is to get an usable result by making as many join as level you're storing.

您当前的模式可能支持多个级别，但是，管理一个或多个级别将非常困难。

Your current schema may support multiple level, however, it will be very difficult to manage more than one or two level.

您可能对嵌套集模型或在mysql中管理层次结构数据

有一些嵌套集的实现，如这一个使用Doctrine 2

There are some implementation of the Nested Set like this one to work with Doctrine 2

这篇关于如何编写一个MYSQL查询，将返回的孩子嵌套在父母下？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！