根据时间、状态和相关行在PostgreSQL中的存在来删除行

create table the_table (
  id integer, root_id integer, parent_id integer, status text, ts timestamp, comment text);
insert into the_table values
(1, null, null, 'COMPLETE', now()-'92d'::interval, '>90 days old, complete, standalone'),
(2, null, null, 'COMPLETE', now()-'92d'::interval, '>90 days old, complete, root of 3,4'),
(3, 2,    null, 'COMPLETE', now()-'92d'::interval, '>90 days old, complete, child of 2, parent of 4'),
(4, 2,    3,    'OPEN',     now()-'92d'::interval, '>90 days old, open, child of 2,3'),
(5, null, null, 'COMPLETE', now()-'92d'::interval, '>90 days old, complete, root of 6,7'),
(6, 5,    null, 'COMPLETE', now()-'92d'::interval, '>90 days old, complete, child of 5, parent of 4'),
(7, 5,    6,    'COMPLETE', now()-'10d'::interval, '<=90 days old, complete, child of 5,6' )

从表中删除所有记录，

• 超过90天
• 处于COMPLETE状态.

如果行具有root_id或parent_id并且是OPEN， 则不应删除第root_id行和第parent_id行.

在上表中，应删除包含id=1的行.

id=2和id=3是COMPLETE和超过90天，但由于id=4是OPEN，具有root_id=2和parent_id=3，这三行(id in (2,3,4))不应被删除.

id=5、id=6和id=7都是COMPLETE，但id=7不超过90天，有root_id=5和parent_id=6，所以这三行(id=5、id=6和id=7)不应该被删除.

我try 了多种方式进行内部查询 但是我不能为这个场景编写一个查询.

DB<>Fiddle个