父子链的SQL查询
我有一个表,可以将表中的另一个成员引用为父级。该父级还可以将另一行引用为其父级......依此类推。
id col1 col2 parentID
1 foo bar NULL
2 blah boo 1
3 fob far 2
4 wob lob NULL
我想返回给定 id 的链。因此,如果 id 为 3,我将返回第 3 行、第 2 行和第 1 行。如果 id 为 2,我将返回第 2 行和第 1 行。如果 id 为 1 或 4,我将仅返回该行。
谢谢
I have a single table that can refer to one other member in the table as a parent. That parent could also refer to one other row as its parent...and so on.
id col1 col2 parentID
1 foo bar NULL
2 blah boo 1
3 fob far 2
4 wob lob NULL
I would like to return the chain given an id. So if the id were 3 I would return row 3, row 2 and row 1. If id was 2 I would return row 2 and row 1. If the id were 1 or 4 I would just return that row.
thank you
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
使用递归CTE:
结果:
Use a recursive CTE:
Results:
如果您使用递归 CTE,请不要忘记添加
连接
,否则您将只是一个 最大递归 - 错误,因为它会循环
IF you use a recursive CTE, don't forget to add
on your join
else you will just a The maximum recursion-error since it loops
干得好
Here you go