递归自我查询 [英] recursive self query

问题描述

我有下表:

myTable:
+----+----------+
| id | parentID |
+----+----------+
|  1 |     null |
|  2 |        1 |
|  3 |        1 |
|  4 |        2 |
|  5 |        4 |
-----------------

我想追溯所有行，直到不再有parentID为止. 所以".... WHERE id = 5" 会给我:

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

推荐答案

您正在使用邻接列表模型.这种递归操作很困难的事实实际上是该模型的主要缺点.

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.

某些DBMS(例如SQL Server 2005，Postgres 8.4和Oracle 11g)支持使用常用表表达式的递归查询和WITH关键字.

Some DBMSes, such as SQL Server 2005, Postgres 8.4 and Oracle 11g, support recursive queries using common table expressions with the WITH keyword.

对于MySQL，您可能有兴趣查看以下介绍替代模型的文章(嵌套设置模型)，这使得递归操作更容易(可能):

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):

• Mike Hillyer:在MySQL中管理分层数据

此外，我还建议查看 Bill Karwin的演示文稿指出在上面的评论中.所描述的闭合表模型是嵌套集的非常有效的替代方法.

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.

这篇关于递归自我查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！