删除SQL表中的分层数据 | 删除SQL表中的分层数据

本文介绍了删除SQL表中的分层数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个包含分层数据的表格.
列ParentId"，其中包含其父项的 Id(ID" - 键列).

I have a table with hierarchical data.
A column "ParentId" that holds the Id ("ID" - key column) of it's parent.

删除一行时，我想删除所有子项(所有级别的嵌套).

When deleting a row, I want to delete all children (all levels of nesting).

怎么做?

谢谢

推荐答案

当行数不是太大时，erikkallen 的递归方法有效.

When the number of rows is not too large, erikkallen's recursive approach works.

这是使用临时表收集所有子项的替代方法:

Here's an alternative that uses a temporary table to collect all children:

create table #nodes (id int primary key)
insert into #nodes (id) values (@delete_id)
while @@rowcount > 0
    insert into #nodes
    select distinct child.id
    from table child
    inner join #nodes parent on child.parentid = parent.id
    where child.id not in (select id from #nodes)

delete
from table
where id in (select id from #nodes)

它从带有@delete_id 的行开始并从那里下降.where 语句是为了防止递归；如果您确定没有，则可以将其省略.

It starts with the row with @delete_id and descends from there. The where statement is to protect from recursion; if you are sure there is none, you can leave it out.

这篇关于删除SQL表中的分层数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！