◆sql语句递归查询和删除问题◆

Question

如下表：Table，有三个字段：
ID       Int            not   null   primarykey   
name     varchar(20)    not   null
parentID int            not   nullparentID（父ID）的值表示该记录的上级记录的ID值，比如：
ID   name   parentID
1    工具    0
2    工具1   1
3    工具2   1
4    工具3   1
6    工具21  2
7    工具311 3
8    工具211 6
9    系统    0
10   系统1   9

现在我想查询或者删除id=1以及其下的子记录，该怎样写sql语句，我使用sqlserver 2000

Answer 1

CREATE TABLE tb(ID int,PID int,Name nvarchar(10))
INSERT tb SELECT 1,NULL,'山东省'
UNION ALL SELECT 2,1   ,'烟台市'
UNION ALL SELECT 4,2   ,'招远市'
UNION ALL SELECT 3,1   ,'青岛市'
UNION ALL SELECT 5,NULL,'四会市'
UNION ALL SELECT 6,5   ,'清远市'
UNION ALL SELECT 7,6   ,'小分市'
GO--删除处理触发器(同步删除被删除节点的所有子节点)
CREATE TRIGGER tr_DeleteNode ON tb
FOR DELETE
AS
IF @@ROWCOUNT=0 RETURN --如果没有满足删除条件的记录,直接退出
--查找所有被删除节点的子节点
DECLARE @t TABLE(ID int,Level int)
DECLARE @Level int
SET @Level=1
INSERT @t SELECT a.ID,@Level
FROM tb a,deleted d
WHERE a.PID=d.ID
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t SELECT a.ID,@Level
FROM tb a,@t b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
DELETE a
FROM tb a,@t b
WHERE a.ID=b.ID
GO--删除
DELETE FROM tb WHERE ID in(2,3,5)
SELECT * FROM tb
/*--结果
ID          PID        Name 
---------------- ----------------- ---------- 
1           NULL      山东省
--*/

Answer 2

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 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
gocreate 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 tbdrop table tb

Answer 3

或者 升级到 2008 吧 这样代码 可能 很简单 (CTE With)