如何通过查询mysql中的自引用表获得递归结果？
我有一个自引用表“comments”，其中 comments.replyToId 引用 comments.ID。
我的问题是，如何使用自引用表查询数据库以获得正确排序的结果，以便我可以在 PHP 中将结果表示为树？
我尝试
select * from comments as comments_1
left join comments as comments_2
on comments_1.id = comments_2.replyToId
过在 php 中使用它的结果
I have a self-referencing table 'comments' where comments.replyToId REFERENCES comments.ID.
My question is, how do I query a database with a self-referencing table to get a result that is properly ordered so that I can represent the result as a tree in PHP?
I've tried
select * from comments as comments_1
left join comments as comments_2
on comments_1.id = comments_2.replyToId
I'm trying to use the result of this in php
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您不会直接从 MySQL 获得递归结果。最近有一个类似的讨论 - 某些 RDBMS 可能使用存储过程等，但不能使用开箱即用的 SQL（请参阅 如何获取任意的祖先ID一个 SQL 查询中的递归深度？）。
在类似情况下我会做什么：在没有父母的情况下获取所有评论。然后，对于每个评论，获取其子项（如果您存储每个评论的“深度”，您可以通过一个 SQL 查询获取所有这些子项以及下一层的所有子项）。将子项存储在树结构中的适当位置，重复。
如果您需要更低级别的，您可能需要共享一些代码，解释您的数据结构，到目前为止您尝试过的内容等，这只是一般方法。
You're not going to get a recursive result out of MySQL directly. There was a similar discussion recently - it is maybe possible with some RDBMS using stored procedures etc, but not with out-of-the-box SQL (see How can I get ancestor ids for arbitrary recursion depth in one SQL query?).
What I do instead in similar cases: Get all comments without parents. Then, for each comment, get its children (if you store the "depth" of each comment you may get all these children and all children of the next layers with one SQL query). Store the children in the appropriate place in your tree structure, repeat.
If you need a more low-level, you'll prly need to share some code, explain your data structure, what you've tried so far etc., this is just the general approach.