MySQL自连接
我有一个如下表（无法更改）：
POST_ID | PARENT_ID | POST_NAME
1 | 0 | Services
4 | 1 | Development
5 | 4 | Magento
2 | 0 | Contact
字段“parent_id”引用 post_id 形成自引用外键。是否可以编写一个查询来根据 post_id 连接帖子及其父级？
例如，如果我有 post_id 5 (Magento)，我可以编写一个查询来产生以下结果：
5 | Magento
4 | Development
1 | Services
我知道这对于多个查询来说很容易做到，但是，希望知道是否可以使用单个查询。
谢谢 ：）
I have a table (this cannot be changed) like the following:
POST_ID | PARENT_ID | POST_NAME
1 | 0 | Services
4 | 1 | Development
5 | 4 | Magento
2 | 0 | Contact
The field, 'parent_id' references post_id to form a self-referential foreign key. Is it possible to write a single query to connect a post and it's parents based on the post_id?
For example, if I had the post_id 5 (Magento), could I write a query to produce the following results:
5 | Magento
4 | Development
1 | Services
I know this is quite easy to do with multiple queries, however, wish to know whether it is possible with a single query.
Thanks :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您正在使用邻接列表模型来组织分层数据。事实上，这种递归操作很困难，这是该模型的一个主要缺点。
某些 DBMS，例如 SQL Server 2005、Postgres 8.4 和 Oracle 11g，支持使用公用表表达式 与
WITH关键字（另请参阅下面的 @Quassnoi 的 评论）。此功能允许轻松编写此类查询，但正如上面评论中提到的 @OMG Ponies ， MySQL 还不支持递归查询。您提到您无法对表格进行任何更改，但是您可以添加额外的表格吗？如果是，您可能有兴趣查看以下文章，其中描述了替代模型（嵌套集合模型），这使得递归操作更容易（可能）：
另外，我还建议查看 @Bill Karwin，Stack Overflow 上的定期贡献者：
演示中描述的闭包表模型是嵌套集的非常有效的替代方案。他在他的 SQL Antipatterns 一书中进一步描述了这个模型 (有关此主题的章节摘录 [PDF]）。
否则，您可能需要在应用程序中执行递归部分，下载所有数据，构建树，然后遍历它。
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 (also see @Quassnoi's comment below). This feature allows queries such as this to be written with ease, but as @OMG Ponies mentioned in a comment above, MySQL does not support recursive queries yet.You mentioned that you cannot make any changes to your table, but can you add an additional table? If yes, you may 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 the following presentation by @Bill Karwin, a regular contributor on Stack Overflow:
The closure table model described in the presentation is a very valid alternative to the nested set. He further describes this model in his SQL Antipatterns book (excerpt from the chapter on this topic [PDF]).
Otherwise, you may want to do the recursive part in your application, downloading all the data, building a tree, and walking through it.
当然，由于您使用的是
MySQL，我建议您将数据模型更改为嵌套集或物化路径。但是，如果您无法使用邻接列表，可以使用以下方法来查询它（需要创建用户定义的函数）：
Of course, since you are using
MySQL, I would suggest to change you data model tonested setsormaterialized path.However, if you are stuck with adjacency list, here's a way to query it (requires creating a user defined function):