如何获得“链接” SQL 查询中的项目
我在 SQL 数据库表中创建了一个“自下而上”的树，其中每个元素都有一个列用于引用其父元素。现在我试图找出是否有一个简单的查询可以获取孩子的所有父母。
例如：
[ Parent #1 ]
|
[Parent #2] [Parent #3]
| |
[Child # 1] [Child #2] [Child #3] [Child #4]
我将 Child #1 作为“ID”。每个条目都有对其父项的引用，并且最顶层父项的父项 id 为空。因此，我不是在代码中递归并获取每个父级，而是希望有一个查询可以通过仅提供子级 1 id 来获取父级 1 id、父级 2 id 和子级 1 id。
I have created a 'bottom-up' tree in my SQL database tables where each element has a column for a reference to its parent. Now I am trying to find out if there is an easy query to get all the parents of a child.
For example:
[ Parent #1 ]
|
[Parent #2] [Parent #3]
| |
[Child # 1] [Child #2] [Child #3] [Child #4]
I am giving Child #1 as the 'ID'. Each entry has reference to it's parent, and the topmost parent is null for parent id. So instead of in my code recursing and getting each parent, I was hoping there was a query I could do that would get Parent 1 id, parent 2 id, and child 1 id by just providing child 1 id.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您希望使用分层查询，并使用 START WITH 和 CONNECT BY 子句。请参阅此链接。
You'd want to use a hierarchical query, using the START WITH and CONNECT BY clauses. See this link.
这只是一个层次检索
我猜到了一个查询，所以欢迎改进。
选择node_id来自树
哪里 child_id="child_1"
从 id="child_1" 开始
按先前的方式连接parent_id=child_id;
It's just a Hierarchical Retrieval
I guessed a query , so improvements are welcome.
SELECT node_idFROM tree
WHERE child_id="child_1"
START WITH id="child_1"
CONNECT BY PRIOR parent_id=child_id;
您还可以使用嵌套集或邻接模型。两者都具有可与任何 SQL 数据库配合使用的优势。邻接模型仅适用于少量有限的嵌套深度，而嵌套集可以嵌套无限的深度。
You also can use a nested set or adjacency model. Both have the advantage to work with any SQL database. The adjacency model works only good with a small limited number of nesting depth, while a nested set can be nested with an unlimited depth.