递归自查询
我有下表：
myTable:
+----+----------+
| id | parentID |
+----+----------+
| 1 | null |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 4 |
-----------------
我想追踪所有行，直到不再有parentID。 所以 "...WHERE id=5" 会给我：
5, 4, 2, 1
I have the following table:
myTable:
+----+----------+
| id | parentID |
+----+----------+
| 1 | null |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 4 |
-----------------
i would like to get all rows tracing back until there's no parentID anymore.
So ".... WHERE id=5" would give me:
5, 4, 2, 1
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您正在使用邻接列表模型组织分层数据。事实上，这种递归操作很困难，这是该模型的一个主要缺点。
某些 DBMS，例如 SQL Server 2005、Postgres 8.4 和 Oracle 11g，支持使用公用表表达式 与
WITH关键字。至于 MySQL，您可能有兴趣查看以下文章，其中描述了替代模型（嵌套集合模型< /a>），这使得递归操作更容易（可能）：
此外，我还建议查看 Bill Karwin 的演示在上面的评论中指出。所描述的闭包表模型是嵌套集的非常有效的替代方案。
You are organizing your hierarchical data using the adjacency list model. The fact that such recursive operations are difficult is in fact one major drawback of this model.
Some DBMSes, such as SQL Server 2005, Postgres 8.4 and Oracle 11g, support recursive queries using common table expressions with the
WITHkeyword.As for MySQL, you may be interested in checking out the following article which describes an alternative model (the nested set model), which makes recursive operations easier (possible):
In addition, I also suggest checking out Bill Karwin's presentation pointed out in the comments above. The closure table model described is a very valid alternative to the nested set.