MySQL SELECT树的父ID [英] MySQL SELECT Tree Parent IDs

问题描述

如何对SELECT语句的记录进行排序，以便它们代表有效的树?

How can I sort the records of a SELECT statement so that they represent a valid tree?

我所有的尝试都显示了嵌套在错误父节点下的子节点.实现此订购的最可靠方法是什么?

All of my attempts show sub-nodes nested under wrong parent nodes. What is the most reliable way to achieve this ordering?

数据

ID      Parent ID      Title
--------------------------------------------
0       NULL           Root
1       0              Node A
2       0              Node B
3       1              Sub-Node C
4       1              Sub-Node D
5       3              Sub-Node E

输出

ID      Parent ID      Title
--------------------------------------------
0       NULL           Root
1       0              Node A
3       1              Sub-Node C
5       3              Sub-Node E
4       1              Sub-Node D
2       0              Node B

数据可视化

Root
    Node A
        Sub-Node C
            Sub-Node E
        Sub-Node D
    Node B

推荐答案

按照@Blindy的建议，我已经用PHP实现了这种排序.这是两个似乎可以相对轻松解决此问题的函数.

Following the advice of @Blindy I have implemented this sort with PHP. Here are the two functions that seem to solve this issue relatively easily.

protected function _sort_helper(&$input, &$output, $parent_id) {
    foreach ($input as $key => $item)
        if ($item->parent_id == $parent_id) {
            $output[] = $item;
            unset($input[$key]);

            // Sort nested!!
            $this->_sort_helper(&$input, &$output, $item->id);
        }
}

protected function sort_items_into_tree($items) {
    $tree = array();
    $this->_sort_helper(&$items, &$tree, null);
    return $tree;
}

如果有一种更简单的方法，我很想听听，但这似乎行得通.

I would be interested to hear if there is a simpler approach, but this does seem to work.

这篇关于MySQL SELECT树的父ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！