T_Sql 处理递归操作

数据库表数据：

1.使用CTE查询

--1.指定ID ，当前项+向下查询所有的子节点
with temp
as
(
   select * from Tree where NodeID=4
   union all
   select t.* from Tree t,temp p
   where t.ParentID=p.NodeID
)
select * from temp;
--2.指定ID，当前项+向上查询所有的父级节点
with temp
 as
 (
   select * from Tree where NodeID=10
   union all
   select t.* from Tree t,temp p
   where t.NodeID=p.ParentID
 )
 select * from temp;

  

2.使用函数查询

--1.指定ID ，获取当前项+向下查询所有的子节点

-------------使用函数查询树结构-------------
alter function fun_getChild(@id int)
returns @tree table(NodeID int,NodeName nvarchar(20),ParentID int,[Level] int)
as
begin
  declare @Level int; set @Level=0;
  insert into @tree select NodeID,NodeName,ParentID ,@Level from Tree
  where NodeID=@id;
  while(@@ROWCOUNT!=0)
	  begin
		set @Level=@Level+1;
		insert into @tree select NodeID,NodeName,ParentID ,@Level from Tree
		where ParentID in(
		  select NodeID  from @tree where Level=@Level-1
		)
	  end;
  return;
end;

select * from dbo.fun_getChild(4);

--2.指定ID，获取当前项+向上查询所有的父级节点

alter function fun_getParent(@id int)
returns @tree table(NodeID int,NodeName nvarchar(20),ParentID int,[Level] int)
as
begin
  declare @Level int; set @Level=0;
  insert into @tree select NodeID,NodeName,ParentID,@Level from Tree
  where NodeID=@id;
  while(@@ROWCOUNT!=0)
	begin
	set @Level=@Level+1;
	insert into @tree select NodeID,NodeName,ParentID,@Level from Tree
	where NodeID=(
	select ParentID  from @tree where Level=@Level-1
	)
	end;
  return;
end;

select * from dbo.fun_getParent(10);

--3，指定ID，获取其他同级节点

alter function fun_getSiblings(@id int)
returns table
as
return	select * from Tree where ParentID=(
	 select ParentID from Tree where NodeID=@id
	) and NodeID!=@id;
	
select * from dbo.fun_getSiblings(1);

--4.指定ParentID，判断TargetID，是否是子节点

create function fun_isChild(@ParentID int,@TargetID int)
returns bit
as
begin
	---过滤相同情况
	if(@ParentID=@TargetID)return 0;
	---定义表变量，来存储刚查过的子节点
	declare @tree table(NodeID int,ParentID int,[Level] int);
	declare @Level int; set @Level=0;
	insert into @tree select NodeID,ParentID,@Level  from Tree where NodeID=@ParentID;
	while(@@ROWCOUNT!=0)
		begin
		---判断是否已经是子节点
		declare @result int;
	    select @result=COUNT(*) from @tree where NodeID=@TargetID;
		if(@result>0)
			return 1;
		-- @@ROWCOUNT 需要紧跟insert语句
		set @Level=@Level+1;
		insert into @tree select NodeID,ParentID,@Level from Tree where ParentID in(
		 select NodeID from @tree where Level=@Level-1
		);
		end;
	---循环结束
	return 0;
end;

select  dbo.fun_isChild(4,5);

--5.指定ChildID，判断TargetID，是否是父节点

create function fun_isParent(@ChildID int,@TargetID int)
returns bit
as
begin
 ---过滤相同情况
 if(@ChildID=@TargetID)return 0;
 --定义表变量，存储刚查过的数据
 declare @tree table(NodeID int,ParentID int,[Level] int);
 declare @Level int;set @Level=0;
 insert into @tree select NodeID,ParentID,@Level  from Tree where NodeID=@ChildID;
 while(@@ROWCOUNT!=0)
	begin
	---先判断是否已经存在
	if(exists(select * from @tree where NodeID=@TargetID))
	return 1;
	set @Level=@Level+1;
	insert into @tree select NodeID,ParentID,@Level from Tree where NodeID=(
	 select ParentID  from @tree where Level=@Level-1
	);
	end;
 return 0;
end;


select dbo.fun_isParent(8,4);