有没有更好的方法来构建此查询？

I have a table like

我有一张桌子

      CommentPaths
============================
ancestor_id | descendant_id
============================
     1            1
     1            2
     1            3
     1            4
     1            5
     1            6
     1            7
     2            2 
     2            4 
     2            5
     4            4
     5            5
     3            3
     3            6
     3            7 
     6            6
     7            7

intended to represent a tree structure like

旨在表示像树的结构

        1
      /   \
     2     3
    / \     \
   4   5     6
            /
           7

Suppose I delete 2. Then I need to delete the records pointed to below.

假设我删除2.然后我需要删除下面指出的记录。

      CommentPaths
============================
ancestor_id | descendant_id
============================
     1            1   
     1            2   <-------------
     1            3
     1            4
     1            5
     1            6
     1            7
     2            2   <-------------
     2            4   <-------------
     2            5   <-------------
     4            4   <-------------
     5            5   <-------------
     3            3
     3            6
     3            7 
     6            6
     7            7

In other words, when deleting k from the tree I need to

换句话说,当我需要从树中删除k时

1. Delete all rows where ancestor_id equals k

删除ancestor_id等于k的所有行

2. Delete all rows where the descendant_id equals there ancestor_id is one of the ones from 1.

删除descendant_id等于的所有行ancestor_id是1中的一个。

So my query would look something like

所以我的查询看起来像

SELECT descendant_id FROM CommentPaths WHERE ancestor_id=2 AS descs
DELETE FROM CommentPaths WHERE ancestor_id IN descs
DELETE FROM CommentPaths WHERE descendant_id IN descs

or is there a fancier, more compact way of doing it????

还是有更高档,更紧凑的做法?

1 个解决方案

#1

-- delete relation from tree
with tree (commentTreeID, ancestor, descendant , path,  src) as 
(
select id,  ancestor_id,  descendant_id , cast ( '-'+ cast(id as varchar(2000)) +'-' as varchar(2000))   , 0from 
CommentPath ct
where ct.ancestor_id = 2
union all
select CT.Id, CT.ancestor_id,  CT.descendant_id ,cast( t.path + '-' + cast(id as varchar(2000)) +'-' as varchar(2000)), 1
from tree t
join CommentPath CT 
 on CT.ancestor_id = t.descendant and
  CHARINDEX (cast( '-' + cast(id as varchar(2000)) +'-' as varchar(2000)), t.path) = 0 and
  t.src != 2
union all
 select CT.Id, CT.descendant_id,  CT.ancestor_id ,cast( t.path + '-' + cast(id as varchar(2000)) +'-' as varchar(2000)), 2
from tree t
join CommentPath CT 
 on CT.descendant_id = t.descendant and
 CHARINDEX(cast( '-' + cast(id as varchar(2000)) +'-' as varchar(2000)), t.path) =0  and
   t.src != 2
 ) 
 delete CT 
  from CommentPath CT 
  join tree t 
  on t.commentTreeID = CT.ID;

• The first join is used to go down on tree (src = 1) .

第一个连接用于向下到树(src = 1)。

• The second join is used to go up on tree (src = 2 ).

第二个连接用于在树上(src = 2)。

When we go up we don't want to go down or up again = > t.src != 2 makes it happen.

当我们上升时,我们不想再次下降或上升=> t.src!= 2使它成为现实。

• CHARINDEX (... ) protects us from loops.

CHARINDEX(...)保护我们免受循环。

Please refer to my solution that I have posted already there. It shows also how to handle Comment table.

请参考我已在那里发布的解决方案。它还显示了如何处理Comment表。

And here link in SQL Fiddle to show some sample that shows what it deletes.

这里链接SQL Fiddle以显示一些显示删除内容的示例。