database - 在一次查询中按顺序对数据库树结构进行排序？

Question

我希望之前没有人问过这个特定问题(找不到任何类似的问题)。如果是这样，请轻轻地将我链接到该页面。

我正在使用 DB (MySQL - InnoDB) 中的树结构，我有两个表 - nodes 和 node_index。第一个表(节点)保留实际数据，而 node_index 跟踪层次结构。 node_index 表(其中的重要部分)如下所示:

+-------------+-------------+--------+----------+
| node_id     | path        | depth  | order    |
+-------------+-------------+--------+----------+
| 0           | 0           | 1      | 1        |
| 1           | 0.1         | 2      | 1        |
| 2           | 0.2         | 2      | 2        |
| 3           | 0.2.3       | 3      | 1        |
| 4           | 0.2.4       | 3      | 2        |
| 5           | 0.5         | 2      | 3        |
+-------------+-------------+--------+----------+

Node_id是节点表中id的外键。对于本题的上下文，节点表的结构是什么并不重要。

现在，在生成完整树的同时，我可以简单地从按路径排序的 node_index 中选择所有内容，并且仅在一次查询中就获得了正确的结构。问题是，这不符合顺序设置，因为对于每个父级 + 级别组合，这将按节点的 ID 对节点进行排序，我需要更改它并让节点按顺序列排序。例如，如果我交换节点编号 3 和 4 的顺序值，我希望它们显示在同一个位置(在它们的父节点 2 下)，但节点 4 将首先显示。我不能使用“ORDER BY path, order”，因为路径是唯一的并且总是覆盖顺序列。我试图再添加一个名为 parent_path 的列，它只保留到父级的路径并按“ORDER BY parent_path，order”对其进行排序，但这被证明是错误的，因为它对级别进行了分组(首先我先得到所有 -级节点，然后是所有二级节点等)。

所以我的目标是在一个查询中正确地获得树结构( child 在他们的 parent 之下，然后也按顺序列排序，而不是他们的 ID)。我当然可以使用递归并在许多查询中获取结构，但是如果可以使用一个没有顺序列的查询来完成，我想即使有顺序也可以在一个查询中完成此操作。我相信在数据库方面更熟练的人可能会找到一种方法(也许是某种自连接？)。

我已经研究这个问题很长时间了，谷歌搜索和搜索，但似乎没有人有这个问题。每个人都通过递归地使用许多查询来解决它，但我希望数据库变得非常大，有很多深度级别，我需要经常生成树。我认为递归对数据库来说是一种不必要的负担。

很抱歉，如果这个问题已经得到解答，或者我遗漏了一些基本且明显的解决方案。

谢谢，雅各布

Answer 1

您可以做的是创建一个 SortPath 列。通常这会在名称之类的东西上，但在您的情况下，您想要使用 Order 列。它与您的路径列类似，但 node_id 编号已替换为您的订单编号。

我在这个答案中给出了一个如何做到这一点的例子:https://stackoverflow.com/a/2797724/39430 .