闭包表存在多条相同路径时删除指定关联的SQL实现问题

你要删除的是所有经过B->D这条父子边的闭包关联记录，原SQL的问题是没有对路径深度做匹配，导致只要祖先包含B、后代包含D的关联都会被命中，不管是不是走的B->D这条路径，所以才会误筛选出A到D深度为3的记录。

只需在原WHERE条件中新增深度匹配规则即可，修改后的查询SQL如下：

SELECT link.ancestor, link.descendant, link.depth
FROM closure_table p,
     closure_table link,
     closure_table c
WHERE p.ancestor = link.ancestor
  AND c.descendant = link.descendant
  AND p.descendant = 'B'
  AND c.ancestor = 'D'
  -- 新增深度匹配：祖先到B的深度 + B到D的1层 + D到后代的深度 = 关联记录的总深度
  AND link.depth = p.depth + 1 + c.depth;

• 对于B->D的直接关联：p是B->B（深度0），c是D->D（深度0），总深度0+1+0=1，刚好匹配B->D深度为1的待删除记录
• 对于A->D深度为2的关联：p是A->B（深度1），c是D->D（深度0），总深度1+1+0=2，刚好匹配这条待删除记录
• 对于A->D深度为3的关联：走的是A->E->C->D的路径，没有经过B->D边，不满足深度匹配规则，不会被查询命中

执行修改后的SQL返回的结果就是仅需要删除的两条记录：

内容的提问来源于stack exchange，提问作者Zenor27