我有以下層次結構：
A -> E -> C -> D
|
|
|-> B -> D
這是我想出的閉包表：
| Ancestor | Descendant | Depth |
| A | A | 0 |
| B | B | 0 |
| C | C | 0 |
| D | D | 0 |
| E | E | 0 |
| A | E | 1 |
| A | B | 1 |
| A | C | 2 |
| E | C | 1 |
| A | D | 3 |
| E | D | 2 |
| C | D | 1 |
| A | D | 2 |
| B | D | 1 |
我想洗掉之間的聯系B和D，因此，我想洗掉之間的聯系A和D（深度的一個2）。問題是，我不希望洗掉之間的聯系A和D深度的3，因為我沒有洗掉之間的聯系C和D。
目前，這里是列出我要洗掉的鏈接的 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;
但是這個陳述句給了我不想洗掉的行：
| Ancestor | Descendant | Depth |
| A | D | 2 |
| A | D | 3 | <- As said before, I want to keep this one
| B | D | 1 |
uj5u.com熱心網友回復：
您可以選擇具有所有相同祖先-后代對的最小深度的祖先-后代對：
with edges(s, e) as (
-- the pairs to be removed
select 'A', 'D'
union all
select 'B', 'D'
),
n_l as (
select c.* from closure c where c.ancestor != c.descendant
)
select c.* from n_l c where exists (select 1 from edges e where e.s = c.ancestor and e.e = c.descendant)
and c.depth = (select min(c1.depth) from n_l c1 where c1.ancestor = c.ancestor and c1.descendant = c.descendant);
輸出：
| 祖先 | 后裔 | 深度 |
|---|---|---|
| 一種 | D | 2 |
| 乙 | D | 1 |
uj5u.com熱心網友回復：
我想我已經找到了解決方案，對于那些感興趣的人：
declare @Descendant nchar(10) = 'D';
declare @Ancestor nchar(10) = 'B';
with cte as
(
select Ancestor, Depth
from closure_table
where Descendant = @Descendant
and Ancestor = @Ancestor
and Depth = 1
union all
select r.Ancestor, l.Depth 1 as Depth
from cte as l
join closure_table as r on r.Descendant = l.Ancestor
where r.Depth = 1
)
delete closure_table
from closure_table
join cte on cte.Ancestor = closure_table.Ancestor and cte.Depth = closure_table.Depth
where closure_table.Descendant = @Descendant;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/348651.html
標籤：sql PostgreSQL 传递闭包表
上一篇：過濾掉相似值字串sql
下一篇：將文本從json轉換為Date