我必须管理我不是SQL大师,也许这个查询真的很简单,但我无法编写它。基本上所有页面都是使用邻接列表模型排列的(我也在跟踪深度):
+----+---------------+-------------------+-----------+
| 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 |
+----+---------------+-------+-----------+-----------+
请注意,位置在同一父级中是唯一的。
我试图实现的是按父类别和位置排序,但父类别应列在其子类别之前。这是显而易见的,不起作用:
SELECT * FROM PAGE p
ORDER BY parent_id, position
结果是第一个表。我需要的是以下内容:
+----+---------------+-------------------+-----------+
| 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 |
+----+---------------+-------+-----------+-----------+
这在不支持递归的MySQL中可能吗?
当可以限制查询时,可以使用自联接。或者尝试嵌套集或塞尔科树。