regions 表的结构NodeID int 4 ParentID int 4 CREATE PROCEDURE Regions_Delete @ParentID int AS
create table #temp (nodeid int ) insert into #temp select nodeid from regions where nodeid=@ParentID select * into #temp2 from #temp select * into #temp3 from #temp where 1!=1 while (1>0) begin insert into #temp3 select nodeid from regions where parentid in (select nodeid from #temp2) if @@rowcount=0 break insert into #temp select * from #temp3
truncate table #temp2 insert into #temp2 select * from #temp3 truncate table #temp3 end delete from regions where nodeid in (select nodeid from #temp)GO
--测试表及数据 create table ttest(parent int,child int) go insert into ttest(parent,child) select 1,2 union select 2,3 union select 3,4 union select 6,7 union select 7,8 union select 8,9 go--辅助函数 create function f_getchildid(@id int) returns @re table(id int,level int) as begin declare @l int set @l=0 insert @re select child,@l from ttest where parent=@id while @@rowcount>0 begin set @l=@l+1 insert @re select a.child,@l from ttest a join @re b on a.parent=b.id where b.level=@l-1 end delete @re where id=@id return end go --执行 select id from dbo.f_getchildid(1) --删除 drop table ttest drop function dbo.f_getchildid go/*结果 2 3 4 */
--建立測試環境 Create Table Tree (Parent Int, Child Int) --插入數據 Insert Tree Select 1, 2 Union All Select 2, 3 Union All Select 3, 4 Union All Select 6, 7 Union All Select 7, 8 Union All Select 8, 9 Go --建立函數 Create Function GetChild(@Parent Int) Returns @Child Table(Parent Int,Child Int) As Begin Insert @Child Select * From Tree Where Parent=@Parent While @@ROWCOUNT>0 Insert @Child Select B.* From @Child A Inner Join Tree B On A.Child=B.Parent Where B.Parent Not In (Select Distinct Parent From @Child) Return End GO --測試 Select Child From dbo.GetChild(1) Select Child From dbo.GetChild(6) Select Child From dbo.GetChild(2) GO --刪除測試環境 Drop Table Tree Drop Function GetChild Go --結果 /* Child 2 3 4Child 7 8 9 Child 3 4 */
create table A ( parent int, Child int ) insert A select 1,2 insert A select 2,3 insert A select 3,4 insert A select 6,7 insert A select 7,8 insert A select 8,9create Function T_Find(@id int) returns varchar(50) as begin declare @lev int declare @T_SQL varchar(100) declare @T table(parent int,Child int,lev int) set @lev=1 set @T_SQL='' insert @T select parent,Child,@lev from A where parent=@id while @@rowcount>0 begin set @lev=@lev+1 insert @T select parent,Child,@lev from A where parent in(select Child from @T where lev=@lev-1) end select @T_SQL=@T_SQL+ cast(Child as varchar) + ',' from @T return left(@T_SQL,len(@T_SQL)-1) endselect dbo.T_Find(1) --orcreate Function T_Find(@id int) returns @T table(parent int,Child int,lev int) as begin declare @lev int --declare @T_SQL varchar(100) --declare @T table(parent int,Child int,lev int) set @lev=1 --set @T_SQL='' insert @T select parent,Child,@lev from A where parent=@id while @@rowcount>0 begin set @lev=@lev+1 insert @T select parent,Child,@lev from A where parent in(select Child from @T where lev=@lev-1) end return endselect Child from dbo.T_Find(1)
--辅助函数 create function f_getchildid(@id int) returns @re table(id int,level int) as begin declare @l int set @l=0 insert @re select child,@l from ttest where parent=@id while @@rowcount>0 begin set @l=@l+1 insert @re select a.child,@l from ttest a join @re b on a.parent=b.id where b.level=@l-1 end --delete @re where id=@id 这里多余了,忘记删除。 return end go
得到字符串的形式--建立測試環境 Create Table Tree (Parent Int, Child Int) --插入數據 Insert Tree Select 1, 2 Union All Select 2, 3 Union All Select 3, 4 Union All Select 6, 7 Union All Select 7, 8 Union All Select 8, 9 Go --建立函數 Create Function GetChild(@Parent Int) Returns Varchar(1000) As Begin Declare @S Varchar(1000) Declare @Child Table(Parent Int,Child Int) Set @S='' Insert @Child Select * From Tree Where Parent=@Parent While @@ROWCOUNT>0 Insert @Child Select B.* From @Child A Inner Join Tree B On A.Child=B.Parent Where B.Parent Not In (Select Distinct Parent From @Child) Select @S=@S+','+Rtrim(Child) From @Child Order By Child Return(Stuff(@S,1,1,'')) End GO --測試 Select dbo.GetChild(1) As Child Select dbo.GetChild(6) As Child Select dbo.GetChild(2) As Child GO --刪除測試環境 Drop Table Tree Drop Function GetChild Go --結果 /* Child 2,3,4Child 7,8,9Child 3,4 */
CREATE TABLE tb( Parent int,Child int) INSERT tb SELECT 1,2 UNION ALL SELECT 2,3 UNION ALL SELECT 3,4 UNION ALL SELECT 6,7 UNION ALL SELECT 7,8 UNION ALL SELECT 8,9GO--查询指定节点及其所有子节点的函数 CREATE FUNCTION f_Cid(@ID int) RETURNS varchar(800) AS BEGIN declare @t_Level TABLE(ID int,Level int) declare @re varchar(800) select @re='' 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.Child,@Level FROM tb a,@t_Level b WHERE a.Parent=b.ID AND b.Level=@Level-1
END select @re=@re+','+Rtrim(id) from @t_level return(stuff(@re,1,3,'')) END GOselect distinct 子节点=dbo.f_Cid(1) from tb --结果 /* 2,3,4 */
regions 表的结构NodeID int 4
ParentID int 4 CREATE PROCEDURE Regions_Delete
@ParentID int
AS
create table #temp
(nodeid int )
insert into #temp select nodeid from regions where nodeid=@ParentID
select * into #temp2 from #temp
select * into #temp3 from #temp where 1!=1
while (1>0)
begin
insert into #temp3 select nodeid from regions where parentid in (select nodeid from #temp2)
if @@rowcount=0
break
insert into #temp select * from #temp3
truncate table #temp2
insert into #temp2 select * from #temp3
truncate table #temp3
end
delete from regions where nodeid in (select nodeid from #temp)GO
create table ttest(parent int,child int)
go
insert into ttest(parent,child)
select 1,2 union select 2,3 union select 3,4 union select 6,7 union select 7,8 union select 8,9
go--辅助函数
create function f_getchildid(@id int)
returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select child,@l from ttest where parent=@id
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.child,@l from ttest a join @re b on a.parent=b.id where b.level=@l-1
end
delete @re where id=@id
return
end
go
--执行
select id from dbo.f_getchildid(1)
--删除
drop table ttest
drop function dbo.f_getchildid
go/*结果
2
3
4
*/
Create Table Tree
(Parent Int,
Child Int)
--插入數據
Insert Tree Select 1, 2
Union All Select 2, 3
Union All Select 3, 4
Union All Select 6, 7
Union All Select 7, 8
Union All Select 8, 9
Go
--建立函數
Create Function GetChild(@Parent Int)
Returns @Child Table(Parent Int,Child Int)
As
Begin
Insert @Child Select * From Tree Where Parent=@Parent
While @@ROWCOUNT>0
Insert @Child Select B.* From @Child A Inner Join Tree B On A.Child=B.Parent Where B.Parent Not In (Select Distinct Parent From @Child)
Return
End
GO
--測試
Select Child From dbo.GetChild(1)
Select Child From dbo.GetChild(6)
Select Child From dbo.GetChild(2)
GO
--刪除測試環境
Drop Table Tree
Drop Function GetChild
Go
--結果
/*
Child
2
3
4Child
7
8
9
Child
3
4
*/
declare @s varchar(1000)
select @s=''
select @s=@s+','+convert(varchar,id) from dbo.f_getchildid(1)
select stuff(@s,1,1,'')
(
parent int,
Child int
) insert A select 1,2
insert A select 2,3
insert A select 3,4
insert A select 6,7
insert A select 7,8
insert A select 8,9create Function T_Find(@id int)
returns varchar(50)
as
begin
declare @lev int
declare @T_SQL varchar(100)
declare @T table(parent int,Child int,lev int)
set @lev=1
set @T_SQL=''
insert @T select parent,Child,@lev from A where parent=@id
while @@rowcount>0
begin
set @lev=@lev+1
insert @T select parent,Child,@lev from A where parent in(select Child from @T where lev=@lev-1)
end
select @T_SQL=@T_SQL+ cast(Child as varchar) + ',' from @T
return left(@T_SQL,len(@T_SQL)-1)
endselect dbo.T_Find(1)
--orcreate Function T_Find(@id int)
returns @T table(parent int,Child int,lev int)
as
begin
declare @lev int
--declare @T_SQL varchar(100)
--declare @T table(parent int,Child int,lev int)
set @lev=1
--set @T_SQL=''
insert @T select parent,Child,@lev from A where parent=@id
while @@rowcount>0
begin
set @lev=@lev+1
insert @T select parent,Child,@lev from A where parent in(select Child from @T where lev=@lev-1)
end
return
endselect Child from dbo.T_Find(1)
create function f_getchildid(@id int)
returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select child,@l from ttest where parent=@id
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.child,@l from ttest a join @re b on a.parent=b.id where b.level=@l-1
end
--delete @re where id=@id 这里多余了,忘记删除。
return
end
go
Create Table Tree
(Parent Int,
Child Int)
--插入數據
Insert Tree Select 1, 2
Union All Select 2, 3
Union All Select 3, 4
Union All Select 6, 7
Union All Select 7, 8
Union All Select 8, 9
Go
--建立函數
Create Function GetChild(@Parent Int)
Returns Varchar(1000)
As
Begin
Declare @S Varchar(1000)
Declare @Child Table(Parent Int,Child Int)
Set @S=''
Insert @Child Select * From Tree Where Parent=@Parent
While @@ROWCOUNT>0
Insert @Child Select B.* From @Child A Inner Join Tree B On A.Child=B.Parent Where B.Parent Not In (Select Distinct Parent From @Child)
Select @S=@S+','+Rtrim(Child) From @Child Order By Child
Return(Stuff(@S,1,1,''))
End
GO
--測試
Select dbo.GetChild(1) As Child
Select dbo.GetChild(6) As Child
Select dbo.GetChild(2) As Child
GO
--刪除測試環境
Drop Table Tree
Drop Function GetChild
Go
--結果
/*
Child
2,3,4Child
7,8,9Child
3,4
*/
INSERT tb SELECT 1,2
UNION ALL SELECT 2,3
UNION ALL SELECT 3,4
UNION ALL SELECT 6,7
UNION ALL SELECT 7,8
UNION ALL SELECT 8,9GO--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID int)
RETURNS varchar(800)
AS
BEGIN
declare @t_Level TABLE(ID int,Level int)
declare @re varchar(800)
select @re=''
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.Child,@Level
FROM tb a,@t_Level b
WHERE a.Parent=b.ID
AND b.Level=@Level-1
END
select @re=@re+','+Rtrim(id) from @t_level
return(stuff(@re,1,3,''))
END
GOselect distinct 子节点=dbo.f_Cid(1) from tb
--结果
/*
2,3,4
*/