如何在嵌套集中查找特定 level2 节点的特定子节点

我有一个标准的嵌套集模型，每个节点都有 name、lft 和 rgt 属性。

我可以使用以下方法找到特定员工的上级：

SELECT P2.* FROM Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
AND P1.name = "Bob"
ORDER BY P2.lft

我还可以通过添加限制和偏移量来找到二级管理器：

SELECT P2.* FROM Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
AND P1.name = "Bob"
ORDER BY P2.lft
LIMIT 2 OFFSET 1

在某些情况下，我可能有多个名为“Bob”的员工在不同的二级经理下工作。二级经理的名字是唯一的。

我需要一个查询，返回名为“Susan”的二级经理的所有名为“Bob”的员工。

编辑：我的查询只需要返回“在“玛丽”下工作的“鲍勃”，因为他是唯一一个拥有名为“苏珊”的二级经理的人。

   Big Boss
   /      \
Brian   Susan
  |       |
Susan   Mary
  |       |
 Bob     Bob

将数字放入：

  1 Big Boss 14
   /        \
2 Brian 7  8 Susan 13
  |           |
3 Susan 6  9 Mary 12
  |           |
4 Bob 5    10 Bob 11

如果我们可以指定我们知道它是“Susan”8 then:

SELECT employee.* 
FROM Personnel AS employee
inner join Personnel AS manager on employee.lft BETWEEN manager.lft
                                                    AND manager.rgt
WHERE 
  employee.name = 'Bob'
  and manager.name = 'Susan'
  and manager.lft = 8
ORDER BY employee.lft

否则，我认为您需要更多地了解该记录，以指定您所说的是哪位经理。

编辑：使查询变得更复杂一点，我使用来自的建议计算了深度本文 http://falsinsoft.blogspot.com/2013/01/tree-in-sql-database-nested-set-model.html. The fiddle http://sqlfiddle.com/#!2/fc0d9/11因为这也有效。

select
  employee.*
from
  Personnel employee
  inner join 
    (SELECT node.name, node.lft, node.rgt, (COUNT(parent.name) - 1) AS depth
     FROM Personnel AS node
     inner join Personnel AS parent on node.lft BETWEEN parent.lft AND parent.rgt
     GROUP BY node.name, node.lft, node.rgt) as manager
                 on employee.lft between manager.lft and manager.rgt    
where
  employee.name = 'bob'
  and manager.name = 'susan'
  and manager.depth = 1