如果我有以下模式和数据并且正在使用闭包表模式:
+----+----------+------------+--------+
| 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 2
的所有同级行?
+----+----------+------------+--------+
| id | ancestor | descendant | length |
+----+----------+------------+--------+
| 3 | 2 | 13 | 1 |
| 4 | 2 | 14 | 1 |
| 5 | 2 | 15 | 1 |
+----+----------+------------+--------+
给定节点的兄弟姐妹将具有相同的祖先。 但是,这将包括"1"以及您的列表:
select t.*
from table t
where t.ancestor = (select ancestor from table t2 where t.id = 2);
在您的表格中,我不确定ancestor
与descendant
相同意味着什么. 但是,我认为以下是您想要的查询:
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;
编辑:
您可以像这样作为显式联接执行此操作:
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"不被视为自身的同级。