declare @t table(id int,parentid int,level int) insert @t select 1 , 0 , 1 union all select 2 , 0 , 1 union all select 3 , 1 , 2 union all select 4 , 1 , 2 union all select 5 , 2 , 2 union all select 6 , 5 , 3 /* 就是一个分类: level 1: 1 2 | | | level 2: 3 4 5 | level 3: 6 我现在想把level 3层中所有与level 1中2相关的所有记录删除*/select * from @t where parentid in(select id from @t where parentid =2)delete from @t where parentid in(select id from @t where parentid =2)select * from @t /*id parentid level ----------- ----------- ----------- 6 5 3(所影响的行数为 1 行) (所影响的行数为 1 行)id parentid level ----------- ----------- ----------- 1 0 1 2 0 1 3 1 2 4 1 2 5 2 2 */
create table t1(id int identity(1,1),parentid int,level int) insert t1 select 0, 1 union all select 0, 1 union all select 1, 2 union all select 1, 2 union all select 2, 2 union all select 3, 3if exists(select 1 from sysobjects where id=object_id(N'f_getlevel') and xtype='TF') drop function f_getlevel go create function f_getlevel( @level int )returns @re table([id] int,[level] int) as begin declare @l int set @l=@level insert @re select [id],@level from t1 where [level]=@level --and [pid]<>0 while @@rowcount>0 begin set @l=@l-1 insert @re select a.parentid,@l from t1 a,@re b where a.[id]=b.[id] and b.[level]=@l+1 and a.parentid<>0 end return end godelete a from t1 a,f_getlevel(3) b where a.[id]=b.[id]
insert @t
select 1 , 0 , 1 union all
select 2 , 0 , 1 union all
select 3 , 1 , 2 union all
select 4 , 1 , 2 union all
select 5 , 2 , 2 union all
select 6 , 5 , 3
/*
就是一个分类:
level 1: 1 2
| | |
level 2: 3 4 5
|
level 3: 6
我现在想把level 3层中所有与level 1中2相关的所有记录删除*/select *
from @t
where parentid in(select id from @t where parentid =2)delete from @t where parentid in(select id from @t where parentid =2)select * from @t
/*id parentid level
----------- ----------- -----------
6 5 3(所影响的行数为 1 行)
(所影响的行数为 1 行)id parentid level
----------- ----------- -----------
1 0 1
2 0 1
3 1 2
4 1 2
5 2 2
*/
insert t1
select 0, 1 union all
select 0, 1 union all
select 1, 2 union all
select 1, 2 union all
select 2, 2 union all
select 3, 3if exists(select 1 from sysobjects where id=object_id(N'f_getlevel') and xtype='TF')
drop function f_getlevel
go
create function f_getlevel(
@level int
)returns @re table([id] int,[level] int)
as
begin
declare @l int
set @l=@level
insert @re select [id],@level from t1 where [level]=@level --and [pid]<>0
while @@rowcount>0
begin
set @l=@l-1
insert @re select a.parentid,@l
from t1 a,@re b
where a.[id]=b.[id] and b.[level]=@l+1 and a.parentid<>0
end
return
end
godelete a from t1 a,f_getlevel(3) b where a.[id]=b.[id]