简单sql删除语句

[sheng9hhd]

我有一表t1
表结构如下（）
id parentid level
1 0 1
2 0 1
3 1 2
4 1 2
5 2 2
6 3 3

就是一个分类：
level 1: 1 2
| | |
level 2: 3 4 5
|
level 3: 6
我现在想把level 3层中所有与level 1中2相关的所有记录删除

[-狙击手-]

delete from @t where parentid in(select id from @t where parentid =2)

[msjqd]

可以做一个递规函数

[-狙击手-]

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
*/

[wgsasd311]

不明白什么意思?

[mislrb]

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, 3

if 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
go

delete a from t1 a,f_getlevel(3) b where a.[id]=b.[id]