删除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.