站点层次结构的邻接列表模型，如何以此特定顺序检索所有页面？

I have to admin I'm not a SQL-guru, maybe this query is really simple but I can't manage to write it. Basically all pages are arranged using the adjacency list model (I'm tracking the deep too):

我必须管理我不是SQL-guru，也许这个查询非常简单，但我无法写出来。基本上所有页面都使用邻接列表模型排列（我也跟踪深度）：

+----+---------------+-------------------+-----------+
| id | title         | level | position  | parent_id |
+----+---------------+-------+-----------+-----------+
|  1 | Home          | 0     | 0         | null      |
+----+---------------+-------+-----------+-----------+
|  2 | Resources     | 0     | 1         | null      |
+----+---------------+-------+-----------+-----------+
|  3 | About         | 0     | 2         | null      |
+----+---------------+-------+-----------+-----------+
|  4 | Documents     | 1     | 0         | 2         |
+----+---------------+-------+-----------+-----------+
|  5 | Tutorials     | 1     | 1         | 2         |
+----+---------------+-------+-----------+-----------+

Note that position is unique among the same parent.

请注意，位置在同一父级中是唯一的。

What I'm trying to achieve is to sort by parent and position, but the parent category should be listed right before its children. This is obvious and doesn't work:

我想要实现的是按父级和位置排序，但父类别应该在其子级之前列出。这是显而易见的，不起作用：

SELECT * FROM PAGE p
ORDER BY parent_id, position

And result is the first table. What I need is the following:

结果是第一个表。我需要的是以下内容：

+----+---------------+-------------------+-----------+
| id | title         | level | position  | parent_id |
+----+---------------+-------+-----------+-----------+
|  1 | Home          | 0     | 0         | null      |
+----+---------------+-------+-----------+-----------+
|  2 | Resources     | 0     | 1         | null      | // Parent of 4, 5
+----+---------------+-------+-----------+-----------+
|  4 | Documents     | 1     | 0         | 2         | // Child of Resource
+----+---------------+-------+-----------+-----------+
|  5 | Tutorials     | 1     | 1         | 2         | // Child of Resource
+----+---------------+-------+-----------+-----------+
|  3 | About         | 0     | 2         | null      |
+----+---------------+-------+-----------+-----------+

Is this possible in MySQL, not supporting recursion?

这在MySQL中是否可行，不支持递归？

1 个解决方案

#1

You can use self-joins when you can limit the query. Or try a nested-set or celko-tree.

您可以在限制查询时使用自联接。或者尝试嵌套集或celko树。