--递归删除1 if object_id('dom') is not null drop table dom go create table dom(id int,name varchar(50),parentid int,Level int) insert into dom select 1,'河北省',null,1 insert into dom select 2,'秦皇岛市',1,2 insert into dom select 3,'山海关区',2,3 insert into dom select 4,'海港区',2,3 insert into dom select 5,'北京',null,1 insert into dom select 6,'海淀',5,2 insert into dom select 7,'朝阳',5,2go if object_id('proc_delete_tb') is not null drop proc proc_delete_tb go create proc proc_delete_tb @id int as begin delete dom where id = @id while @@rowcount > 0 delete dom where parentid not in(select id from dom) and parentid is not null end go exec proc_delete_tb 1 select * from dom
--2005的查询 if object_id('tb') is not null drop table tb go create table tb(id int,name varchar(50),parentid int) insert into tb select 1,'安徽省',0 insert into tb select 2,'湖南省',0 insert into tb select 3,'合肥市',1 insert into tb select 4,'巢湖市',1 insert into tb select 5,'肥西县',3 insert into tb select 6,'肥东县',3 insert into tb select 7,'肥东乡',6 insert into tb select 8,'肥东村',7 insert into tb select 9,'湖南乡',2 insert into tb select 10,'湖南镇',9 gowith dom as (select * from tb where parentid=3 union all select a.* from tb a inner join dom b on a.parentid=b.id)select * from dom
SQL2005可以使用CTE:create table Tree(nId smallint, NodeName nvarchar(16), pId smallint) insert Tree select 1, '中国', NULL union all select 2, '北京', 1 union all select 3, '上海', 1 union all select 4, '湖北', 1 union all select 5, '湖南', 1 union all select 6, '武汉', 4 union all select 7, '孝感', 4 union all select 8, '长沙', 5 union all select 9, '岳阳', 5 union all select 10, '海淀区', 2 union all select 11, '朝阳区', 2 union all select 12, '上地', 10 union all select 13, '西三旗', 10go create procedure sp_GetNoedsByRootID @nId smallint as begin with NewTree(nId, NodeName, pId) as ( select * from Tree where nId=@nId union all select T.* from Tree T join NewTree NT on T.pId=NT.nId ) select NT.nId, NT.NodeName, PT.NodeName as [ParentName] from NewTree NT join Tree PT on NT.pId=PT.nId end gocreate procedure sp_DelNoedsByRootID @nId smallint as begin declare @sql nvarchar(max) set @sql=' with NewTree(nId, NodeName, pId) as ( select * from Tree where nId=@nId union all select T.* from Tree T join NewTree NT on T.pId=NT.nId ) delete Tree from Tree T join NewTree NT on T.nId=NT.nId ' exec sp_executesql @sql, N'@nId smallint', @nId end go--查找节点及子节点 exec sp_GetNoedsByRootID 2--删除节点及子节点 exec sp_DelNoedsByRootID 2 select * from Treedrop table Tree drop procedure sp_GetNoedsByRootID, sp_DelNoedsByRootID
SQL2000: --测试数据 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 招远市 --*/
查询到idi下所有的id,然后将其一并删除create procedure pro_delete @id int as begin ;with tt(id,parentid) as ( select id,parentid from tb where id = @id union all select a.id,a.parentid from tb a join tt b on a.parentid = b.id ) delete tb where id in (select id from tt) end
--递归删除1
if object_id('dom') is not null
drop table dom
go
create table dom(id int,name varchar(50),parentid int,Level int)
insert into dom select 1,'河北省',null,1
insert into dom select 2,'秦皇岛市',1,2
insert into dom select 3,'山海关区',2,3
insert into dom select 4,'海港区',2,3
insert into dom select 5,'北京',null,1
insert into dom select 6,'海淀',5,2
insert into dom select 7,'朝阳',5,2go
if object_id('proc_delete_tb') is not null
drop proc proc_delete_tb
go
create proc proc_delete_tb
@id int
as
begin
delete dom where id = @id
while @@rowcount > 0
delete dom where parentid not in(select id from dom) and parentid is not null
end
go
exec proc_delete_tb 1
select * from dom
--2005的查询
if object_id('tb') is not null
drop table tb
go
create table tb(id int,name varchar(50),parentid int)
insert into tb select 1,'安徽省',0
insert into tb select 2,'湖南省',0
insert into tb select 3,'合肥市',1
insert into tb select 4,'巢湖市',1
insert into tb select 5,'肥西县',3
insert into tb select 6,'肥东县',3
insert into tb select 7,'肥东乡',6
insert into tb select 8,'肥东村',7
insert into tb select 9,'湖南乡',2
insert into tb select 10,'湖南镇',9
gowith dom
as
(select * from tb where parentid=3
union all
select a.* from tb a inner join dom b on a.parentid=b.id)select * from dom
insert Tree select 1, '中国', NULL
union all select 2, '北京', 1
union all select 3, '上海', 1
union all select 4, '湖北', 1
union all select 5, '湖南', 1
union all select 6, '武汉', 4
union all select 7, '孝感', 4
union all select 8, '长沙', 5
union all select 9, '岳阳', 5
union all select 10, '海淀区', 2
union all select 11, '朝阳区', 2
union all select 12, '上地', 10
union all select 13, '西三旗', 10go
create procedure sp_GetNoedsByRootID
@nId smallint
as
begin
with NewTree(nId, NodeName, pId) as
(
select * from Tree where nId=@nId
union all
select T.* from Tree T join NewTree NT on T.pId=NT.nId
)
select NT.nId, NT.NodeName, PT.NodeName as [ParentName]
from NewTree NT join Tree PT on NT.pId=PT.nId
end
gocreate procedure sp_DelNoedsByRootID
@nId smallint
as
begin
declare @sql nvarchar(max)
set @sql='
with NewTree(nId, NodeName, pId) as
(
select * from Tree where nId=@nId
union all
select T.* from Tree T join NewTree NT on T.pId=NT.nId
)
delete Tree
from Tree T join NewTree NT on T.nId=NT.nId
'
exec sp_executesql @sql, N'@nId smallint', @nId
end
go--查找节点及子节点
exec sp_GetNoedsByRootID 2--删除节点及子节点
exec sp_DelNoedsByRootID 2
select * from Treedrop table Tree
drop procedure sp_GetNoedsByRootID, sp_DelNoedsByRootID
--测试数据
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 招远市
--*/
查询到idi下所有的id,然后将其一并删除create procedure pro_delete
@id int
as
begin
;with tt(id,parentid)
as
(
select id,parentid from tb where id = @id
union all
select a.id,a.parentid from tb a join tt b on a.parentid = b.id
)
delete tb
where id in (select id from tt)
end