求一个关于树结构的sql语句

Question

有一个表结构如下
ID    NAME      PARID
1      A         0  
2      B         0 
3      C         0 
4      A_1_1     1 
5      A_1_2     1
6      A_2_1     4
7      A_2_2     4
如何删除ID = 1的纪录时，把4,5,6,7记录也全部删除   这个其实就是一个Tree结构，删除根的同时把叶子也删除掉

Answer 1

--查找指定节点的所有子节点的示例函数
--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID   PID  Name       
------ ------- ---------- 
002  001  烟台市
004  002  招远市
--*/

Answer 2

create table tb(id int,name varchar(20),papid int)
insert into tb select 1,'A',0
insert into tb select 2,'B',0
insert into tb select 3,'C',0
insert into tb select 4,'A_1_1',1
insert into tb select 5,'A_1_2',1
insert into tb select 6,'A_2_1',4
insert into tb select 7,'A_2_2',4
drop function f_Cid
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.papid=b.ID
AND b.Level=@Level-1
END
RETURN
ENDdelete a
FROM tb a,f_Cid(4) b
WHERE a.ID=b.IDselect * from tbid name papid
1 A 0
2 B 0
3 C 0
5 A_1_2 1

Answer 3

create table tb(id int,name varchar(20),papid int)
insert into tb select 1,'A',0
insert into tb select 2,'B',0
insert into tb select 3,'C',0
insert into tb select 4,'A_1_1',1
insert into tb select 5,'A_1_2',1
insert into tb select 6,'A_2_1',4
insert into tb select 7,'A_2_2',4
drop function f_Cid
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.papid=b.ID
AND b.Level=@Level-1
END
RETURN
ENDdelete a
FROM tb a,f_Cid(4) b
WHERE a.ID=b.IDselect * from tbid name papid
1 A 0
2 B 0
3 C 0
5 A_1_2 1

Answer 4

--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID int)
RETURNS @t_Level TABLE(ID int,Level int)
AS
BEGIN
    DECLARE @Level int
    SET @Level=1
    INSERT @t_Level SELECT @ID,@Level
    WHILE @@ROWCOUNT>0
    BEGIN
        SET @Level=@Level+1
        INSERT @t_Level SELECT a.ID,@Level
        FROM tb a,@t_Level b
        WHERE a.parid=b.ID
            AND b.Level=@Level-1
    END
    RETURN
END
GOcreate table tb(ID int , NAME varchar(10) , PARID int)
insert into tb values(1,      'A'     ,    0 )  
insert into tb values(2,      'B'     ,    0 ) 
insert into tb values(3,      'C'     ,    0 )
insert into tb values(4,      'A_1_1' ,    1 ) 
insert into tb values(5,      'A_1_2' ,    1 )
insert into tb values(6,      'A_2_1' ,    4 )
insert into tb values(7,      'A_2_2' ,    4 )
godeclare @id as int
set @id = 1 --查询
SELECT a.* FROM tb a,f_Cid(@id) b WHERE a.ID=b.ID
/*
ID          NAME       PARID       
----------- ---------- ----------- 
1           A          0
4           A_1_1      1
5           A_1_2      1
6           A_2_1      4
7           A_2_2      4
（所影响的行数为 5 行）
*/--删除
delete from tb where id in (SELECT a.id FROM tb a,f_Cid(@id) b WHERE a.ID = b.ID)
--查询删除后的结果
select * from tb
/*
ID          NAME       PARID       
----------- ---------- ----------- 
2           B          0
3           C          0
（所影响的行数为 2 行）
*/drop table tb
drop function dbo.f_cid

Answer 5

--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID int)
RETURNS @t_Level TABLE(ID int,Level int)
AS
BEGIN
    DECLARE @Level int
    SET @Level=1
    INSERT @t_Level SELECT @ID,@Level
    WHILE @@ROWCOUNT>0
    BEGIN
        SET @Level=@Level+1
        INSERT @t_Level SELECT a.ID,@Level
        FROM tb a,@t_Level b
        WHERE a.parid=b.ID
            AND b.Level=@Level-1
    END
    RETURN
END
GOcreate table tb(ID int , NAME varchar(10) , PARID int)
insert into tb values(1,      'A'     ,    0 )  
insert into tb values(2,      'B'     ,    0 ) 
insert into tb values(3,      'C'     ,    0 )
insert into tb values(4,      'A_1_1' ,    1 ) 
insert into tb values(5,      'A_1_2' ,    1 )
insert into tb values(6,      'A_2_1' ,    4 )
insert into tb values(7,      'A_2_2' ,    4 )
godeclare @id as int
set @id = 1 --查询
SELECT a.* FROM tb a,f_Cid(@id) b WHERE a.ID=b.ID
/*
ID          NAME       PARID       
----------- ---------- ----------- 
1           A          0
4           A_1_1      1
5           A_1_2      1
6           A_2_1      4
7           A_2_2      4
（所影响的行数为 5 行）
*/--删除
delete from tb where id in (SELECT a.id FROM tb a,f_Cid(@id) b WHERE a.ID = b.ID)
--查询删除后的结果
select * from tb
/*
ID          NAME       PARID       
----------- ---------- ----------- 
2           B          0
3           C          0
（所影响的行数为 2 行）
*/drop table tb
drop function dbo.f_cid

Answer 6

NND!
一个转我的帖,不留我的名.
一个完全拷贝我的帖.
郁闷.

Answer 7

来个原创的吧
create table tb(ID int , NAME varchar(10) , PARID int)
go
insert into tb values(1,      'A'     ,    0 )  
insert into tb values(2,      'B'     ,    0 ) 
insert into tb values(3,      'C'     ,    0 )
insert into tb values(4,      'A_1_1' ,    1 ) 
insert into tb values(5,      'A_1_2' ,    1 )
insert into tb values(6,      'A_2_1' ,    4 )
insert into tb values(7,      'A_2_2' ,    4 )
go
create proc deleteSort
(@id [int] )
AS
BEGIN
declare @i int 
set @i=1
CREATE TABLE #childsorts(
[level][int], 
[SortID] [int] NOT NULL)
insert into #childsorts values(@i,@id)
while(select count(*) from #childsorts where [level]=@i)>0
begin
set @i=@i+1
insert into #childsorts select @i as [level],id from tb 
where  ParID in (select [SortID] from #childsorts where [level]=@i-1)
end
select * from  tb where ID in (select [SortID] from #childsorts)
    --delete tb where ID in (select [SortID] from #childsorts)
ENDexec deleteSort 1
(山之魂原创,著作权所有,转贴须经本人同意!) 

Answer 8

没那么麻烦，也不要函数了，一个循环就行了：
delete tb where id = 1
while @@rowcount > 0
 delete tb where pid not in(select id from tb) and id <> 0

Answer 9

delete tb where id = 1
while @@rowcount > 0
 delete tb where pid not in(select id from tb) and pid <> 0

Answer 10

drop table tb
create table tb(id int,name varchar(20),pid int)
insert into tb select 1,'A',0
insert into tb select 2,'B',0
insert into tb select 3,'C',0
insert into tb select 4,'A_1_1',1
insert into tb select 5,'A_1_2',1
insert into tb select 6,'A_2_1',4
insert into tb select 7,'A_2_2',4delete tb where id = 1
while @@rowcount > 0
 delete tb where pid not in(select id from tb) and pid <> 0/*
服务器: 消息 207，级别 16，状态 3，行 13
列名 'pid' 无效。
服务器: 消息 207，级别 16，状态 1，行 13
列名 'pid' 无效。
*/2000系统.
13楼是在什么系统?

Answer 11

SQL 2000create table tb(ID int , NAME varchar(10) , PID int)
insert into tb values(1,      'A'     ,    0 )  
insert into tb values(2,      'B'     ,    0 ) 
insert into tb values(3,      'C'     ,    0 )
insert into tb values(4,      'A_1_1' ,    1 ) 
insert into tb values(5,      'A_1_2' ,    1 )
insert into tb values(6,      'A_2_1' ,    4 )
insert into tb values(7,      'A_2_2' ,    4 )
go
delete tb where id = 1
while @@rowcount > 0
 delete tb where pid not in(select id from tb) and pid <> 0
select * from tb
/*
ID          NAME       PID         
----------- ---------- ----------- 
2           B          0
3           C          0
*/
drop table tb

Answer 12

星辰技术社区:www.netcsharp.cn,我们将帮您以最快的速度找到最佳的解决方案