使用闭包表时，我将使用哪种查询来获取同级记录? [英] What query would I use to obtain sibling records when using closure tables?

问题描述

如果我具有以下架构&数据并正在使用闭合表模式:

If I have the following schema & data and am employing the closure table pattern:

+----+----------+------------+--------+ | id | ancestor | descendant | length | +----+----------+------------+--------+ | 1 | 2 | 2 | 0 | | 2 | 2 | 12 | 1 | | 3 | 2 | 13 | 1 | | 4 | 2 | 14 | 1 | | 5 | 2 | 15 | 1 | | 10 | 12 | 12 | 0 | | 11 | 13 | 13 | 0 | | 12 | 14 | 14 | 0 | | 13 | 15 | 15 | 0 | | 9 | 17 | 20 | 1 | | 8 | 17 | 19 | 1 | | 7 | 17 | 18 | 1 | | 6 | 17 | 17 | 0 | | 14 | 18 | 18 | 0 | | 15 | 19 | 19 | 0 | | 16 | 20 | 20 | 0 | +----+----------+------------+--------+

+----+----------+------------+--------+ | id | ancestor | descendant | length | +----+----------+------------+--------+ | 1 | 2 | 2 | 0 | | 2 | 2 | 12 | 1 | | 3 | 2 | 13 | 1 | | 4 | 2 | 14 | 1 | | 5 | 2 | 15 | 1 | | 10 | 12 | 12 | 0 | | 11 | 13 | 13 | 0 | | 12 | 14 | 14 | 0 | | 13 | 15 | 15 | 0 | | 9 | 17 | 20 | 1 | | 8 | 17 | 19 | 1 | | 7 | 17 | 18 | 1 | | 6 | 17 | 17 | 0 | | 14 | 18 | 18 | 0 | | 15 | 19 | 19 | 0 | | 16 | 20 | 20 | 0 | +----+----------+------------+--------+

要返回到主表的所有同级行，我的联接查询将如何返回主表?

What would my join query back to my main table look like to obtain all the sibling rows of row id 2?

+----+----------+------------+--------+ | id | ancestor | descendant | length | +----+----------+------------+--------+ | 3 | 2 | 13 | 1 | | 4 | 2 | 14 | 1 | | 5 | 2 | 15 | 1 | +----+----------+------------+--------+

+----+----------+------------+--------+ | id | ancestor | descendant | length | +----+----------+------------+--------+ | 3 | 2 | 13 | 1 | | 4 | 2 | 14 | 1 | | 5 | 2 | 15 | 1 | +----+----------+------------+--------+

推荐答案

给定节点的兄弟姐妹具有相同的祖先.但是，这将包括"1"以及您的列表:

The siblings of a given node would have the same ancestor. However, this would include "1" as well as your list:

select t.*
from table t 
where t.ancestor = (select ancestor from table t2 where t.id = 2);

在您的表中，我不确定ancestor与descendant相同意味着什么.但是，我认为以下是您想要的查询:

In your table, I am not sure what it means for ancestor to be the same as descendant. But, I think the following is the query you want:

select t.*
from table t 
where t.ancestor = (select ancestor from table t2 where t2.id = 2) and
      t.ancestor <> t.descendant and
      t.id <> 2;

您可以像这样显式联接那样执行此操作:

You can do this as an explicit join like this:

select t.*
from table t join
     table t2
     on t.ancestor = t2.ancestor and
        t2.id = 2 a
where t.id <> 2 and
      t.ancestor <> t.descendant;

注意:我还添加了条件t.id <> 2，因此"2"不被视为其同级.

Note: I also added the condition t.id <> 2 so "2" is not considered a sibling of itself.

这篇关于使用闭包表时，我将使用哪种查询来获取同级记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！