（转）sql语句递归查询和删除问题

最新推荐文章于 2025-09-23 13:48:20 发布

转载最新推荐文章于 2025-09-23 13:48:20 发布 · 946 阅读

文章标签：

#sql #工具 #table #insert #delete #function

本文提供了一个使用SQL进行递归查询和删除操作的例子，包括创建表、插入数据及实现递归查询与删除的功能。通过两种不同版本的SQL（2000和2005）展示了如何获取指定节点的所有子节点，并执行相应的查询和删除操作。

SQL code

if object_id('tb') is not null
   drop table tb 
go

create table tb(id int,name varchar(50),parentId int)
go

insert into tb 
select 1 ,   '工具',   0  union all
select 2 ,   '工具1',  1 union all
select 3 ,   '工具2',  1 union all
select 4 ,   '工具3',  1 union all
select 6 ,   '工具21',  2 union all
select 7 ,   '工具311', 3 union all
select 8 ,   '工具211', 6 union all
select 9 ,   '系统',    0 union all
select 10,   '系统1' ,  9 

go 

-- 2000 写法

if object_id('dbo.f_getchirdNode()') is not null
   drop function dbo.f_getchirdNode
go

create function f_getchirdNode(@id int)
returns @t_level table(id varchar(3) , level int)
begin
  declare @level int
  set @level = 1
  insert into @t_level select @id , @level
  while @@ROWCOUNT > 0
  begin
    set @level = @level + 1
    insert into @t_level select a.id , @level
    from tb a , @t_Level b
    where a.parentId = b.id and b.level = @level - 1
  end
  return
end
go
select * from tb a where exists (select * from f_getchirdNode(1) b where a.id = b.id) -- 查询
delete from tb where exists (select * from f_getchirdNode(1) b where tb.id = b.id) -- 删除
go

-- 2005
declare @id int
set @id = 1
;with liang as
(
   select * from tb where id = @id 
   union all
   select a.* from tb a,liang b where a.parentId = b.id
)
delete from tb where exists (select 1 from liang where tb.id = liang.id) -- 删除
--select * from liang --查询
select * from tb

drop table tb