本文介绍了删除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表中的分层数据
发布评论