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
SQL code if object_id(tb) is not null drop table tb gocreate table tb(id int,name varchar(50),parentId int)goinsert into tb select 1 , 工具, 0 union alls