获取父级和父级的所有子级（包括“这一行”）
我有一个自联接场景，其中 linkedAssignment_id 是父级，它本身是一个分配（在分配表中定义）。
我有：
assignment_id: 1 with no parent.
assignment_id: 2 with 1 as parent.
assignment_id: 3 with 1 as parent.
给定 1 或 2 或 3，我想返回 (1, 2, 3)。换句话说，如果给定了父级的主键，我需要获取所有子级+父级的 id。或者，如果给出了 id 或任何孩子，我想返回它的父母和所有兄弟姐妹（包括这个兄弟姐妹）。
我想出了以下内容，它看起来笨重且效率低下，有人可以提供一些指示吗？
select assignment_id
from assignment a
where linkedassignment_id = 3
or assignment_id = 3
union
(
select assignment_id
from assignment a
where linkedassignment_id in (
select linkedassignment_id from assignment b where assignment_id = 3
)
union
select linkedassignment_id from assignment b where assignment_id = 3
)
I have a self-join scenario where linkedAssignment_id is the parent and itself is an assignment (defined in Assignment table).
I have:
assignment_id: 1 with no parent.
assignment_id: 2 with 1 as parent.
assignment_id: 3 with 1 as parent.
Given either 1 or 2 or 3,I want to return (1, 2, 3). In other words, if the primary key of the parent is given I need to get id's of all the children + parent. Or if the id or any child is given, i want to return it's parent and all the siblings (including this sibling).
I came up with the following, it looks clunky and inefficient, can somebody please provide some pointers?
select assignment_id
from assignment a
where linkedassignment_id = 3
or assignment_id = 3
union
(
select assignment_id
from assignment a
where linkedassignment_id in (
select linkedassignment_id from assignment b where assignment_id = 3
)
union
select linkedassignment_id from assignment b where assignment_id = 3
)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您有一个表，其中一条记录引用另一条记录，该记录引用另一条记录，等等（如树中的指针），则没有简单的方法可以使用纯关系方式选择子树。
如果您使用的是 Oracle，则可以使用专为此类情况创建的
connect by运算符。还有其他技术可以帮助实现这一点。最常用的是这些：
将每个节点内根的完整路径存储为文本字符串。假设您的节点有
path = 'foo.bar.baz';这意味着该节点是baz，其父节点是bar，根节点是foo。要有效地选择从bar开始的子树，请使用select ... where path like 'foo.bar.%'。要选择特定节点，请使用完整路径。当然，path列应该被索引。这种方法最适合几层深度的树。使用嵌套集存储树。它有点多毛，但可能更容易操作（例如移动子树）。
If you have a table where one record refers another, that record refers yet another, etc (like pointers in a tree), there's no easy way to select a subtree using purely relational means.
If you were using Oracle, you could turn to its
connect byoperator created specifically for cases like this.Other techniques exist to help this. Most used are these:
Store the entire path to the root inside every node, as a text string. Say, your node has
path = 'foo.bar.baz'; it means that this node isbaz, its parent isbar, and the root isfoo. To efficiently select a subtree starting atbar, useselect ... where path like 'foo.bar.%'. To select a particular node, use a full path. Thepathcolumn should be indexed, of course. This approach works best with trees few levels deep.Store the tree using nested sets. It's a bit more hairy but may be easier to manipulate (e.g. move subtrees around).