--测试数据 create table t(id int, parent_id int, name varchar(50))insert into t select 1, 0 , 'aaa' union all select 2, 0, 'bbb' union all select 3, 1 , 'ccc' union all select 4, 1 , 'ddd' union all select 5 , 3 , 'eee' union all select 6, 3 , 'fff'--建立函数 create function dbo.aa(@i int) returns @t table(id int,parent_id int,level int) as begin declare @level int set @level=0 insert into @t select id,parent_id,@level from t where t.id=@i while @@rowcount>0 begin set @level=@level+1 insert into @t select a.id,a.parent_id,@level from t a left join @t b on a.parent_id=b.id where b.level=@level-1 end return end --删除id为1的节点及子节点 delete t where id in (select id from dbo.aa(1)) -- select * from tid parent_id name ----------- ----------- -------------------------------------------------- 2 0 bbb(所影响的行数为 1 行)
这个不是一条SQL语句能够解决的问题!!你写个存储过程吧: 估计要用到游标!!!!
CREATE PROCEDURE [dbo].[spDeleteAll] @pId int(4) --要删除的记录id AS BEGIN SET NOCOUNT ON; delete from table where parent_id=@pId if @@error=0 begin delete from table where id=@pId end END GO
还可以再改进CREATE PROCEDURE [dbo].[spDeleteAll] @pId int(4) AS BEGIN SET NOCOUNT ON; declare @cnt int --用于保存是否有子记录 select @cnt=count(1) from table where parent_id=@pId if @cnt>0 --如果有子记录 begin delete * from table where parent_id=@pId if @@error=0 begin delete * from table where id=@pId end end else begin delete * from table where id=@pId end END GO 当然,还可以再改进,比如加入事务处理等等
--测试数据 create table t(id int, parent_id int, name varchar(50))insert into t select 1, 0 , 'aaa' union all select 2, 0, 'bbb' union all select 3, 1 , 'ccc' union all select 4, 1 , 'ddd' union all select 5 , 3 , 'eee' union all select 6, 3 , 'fff'--建立函数 create function dbo.aa(@i int) returns @t table(id int,parent_id int,level int) as begin declare @level int set @level=0 insert into @t select id,parent_id,@level from t where t.id=@i while @@rowcount>0 begin set @level=@level+1 insert into @t select a.id,a.parent_id,@level from t a left join @t b on a.parent_id=b.id where b.level=@level-1 end return end --删除id为1的节点及子节点 delete t where id in (select id from dbo.aa(1)) -- select * from tid parent_id name ----------- ----------- -------------------------------------------------- 2 0 bbb(所影响的行数为 1 行)
其实也可以使用一个简单的做法, delete from table where id = 1; //删除主记录然后根据有多少层多次执行删除语句,删除父点不存在的节点即可。 delete from table where pid not in (select id from table )
delete from table1 where id=1 or parent_id=1 ;
create table t(id int, parent_id int, name varchar(50))insert into t
select 1, 0 , 'aaa' union all select
2, 0, 'bbb' union all select
3, 1 , 'ccc' union all select
4, 1 , 'ddd' union all select
5 , 3 , 'eee' union all select
6, 3 , 'fff'--建立函数
create function dbo.aa(@i int)
returns @t table(id int,parent_id int,level int)
as
begin
declare @level int
set @level=0
insert into @t
select id,parent_id,@level from t where t.id=@i
while @@rowcount>0
begin
set @level=@level+1
insert into @t
select a.id,a.parent_id,@level from t a left join @t b on a.parent_id=b.id
where b.level=@level-1
end
return
end
--删除id为1的节点及子节点
delete t
where id in (select id from dbo.aa(1))
--
select * from tid parent_id name
----------- ----------- --------------------------------------------------
2 0 bbb(所影响的行数为 1 行)
估计要用到游标!!!!
@pId int(4) --要删除的记录id
AS
BEGIN SET NOCOUNT ON; delete from table where parent_id=@pId
if @@error=0
begin
delete from table where id=@pId
end
END
GO
@pId int(4)
AS
BEGIN
SET NOCOUNT ON;
declare @cnt int --用于保存是否有子记录
select @cnt=count(1) from table where parent_id=@pId
if @cnt>0 --如果有子记录
begin
delete * from table where parent_id=@pId
if @@error=0
begin
delete * from table where id=@pId
end
end
else
begin
delete * from table where id=@pId
end
END
GO
当然,还可以再改进,比如加入事务处理等等
------------------
顶他
---------------
kelph() ( ) 信誉:100 2007-09-20 09:42:38 得分: 0
--测试数据
create table t(id int, parent_id int, name varchar(50))insert into t
select 1, 0 , 'aaa' union all select
2, 0, 'bbb' union all select
3, 1 , 'ccc' union all select
4, 1 , 'ddd' union all select
5 , 3 , 'eee' union all select
6, 3 , 'fff'--建立函数
create function dbo.aa(@i int)
returns @t table(id int,parent_id int,level int)
as
begin
declare @level int
set @level=0
insert into @t
select id,parent_id,@level from t where t.id=@i
while @@rowcount>0
begin
set @level=@level+1
insert into @t
select a.id,a.parent_id,@level from t a left join @t b on a.parent_id=b.id
where b.level=@level-1
end
return
end
--删除id为1的节点及子节点
delete t
where id in (select id from dbo.aa(1))
--
select * from tid parent_id name
----------- ----------- --------------------------------------------------
2 0 bbb(所影响的行数为 1 行)
delete from table where id = 1; //删除主记录然后根据有多少层多次执行删除语句,删除父点不存在的节点即可。
delete from table where pid not in (select id from table )