SQLSERVER实现递归
2017-04-05 09:25:26

1、依据父节点查找所有子节点

```create  function f_GetAllChildrenAreaById(@id int)                  --得到一个地区的所有下级
returns @temp_tb_areas table (id int,vName varchar(50),iParentId int ,iLevel int) as
begin
insert @temp_tb_areas select id,vName,iParentId,iLevel from tb_Areas where id = @id
while @@rowcount > 0
begin
insert @temp_tb_areas select id,vName,iParentId,iLevel from tb_Areas where iParentId in (select id from @temp_tb_areas) and id not in (select id from @temp_tb_areas)
end
return
end```

`select * from f_GetAllChildrenAreaById(1)`

2、依据子节点查找所有父节点

```create function [dbo].[f_GetAllParentAreaById](@id int)
returns @temp_tb_areas table(Id int,vName varchar(50),iParentId int,iLevel int)
as
begin
insert into @temp_tb_areas select id,vName,iParentId,iLevel from tb_Areas where ID=@id
while @@rowcount<>0
begin
insert into @temp_tb_areas select a.Id,a.vName,a.iParentId,a.iLevel from tb_Areas as a
inner join @temp_tb_areas as b
on a.Id=b.iParentId
and not exists(select 1 from @temp_tb_areas where iParentId=a.iParentId)
end
return
end ```

`  select * from [f_GetAllParentAreaById](13)`

1、依据父节点、查找所有子节点，代码如下：

```create PROCEDURE [dbo].[GetAllChildrenAreaById]
@id int
AS
BEGIN
with cte as
(
select ID,vName,iParentId,iLevel from tb_Areas where ID = @id
union all
select a.ID,a.vName,a.iParentId,a.iLevel from tb_Areas a join cte b on a.iParentId = b.Id
)
select * from cte order by ID asc
end```

2、依据子节点查找所有父节点

```create PROCEDURE [dbo].[GetAllParentById]
@id int
AS
BEGIN
with cte (id,iParentId,vName,iLevel)
as
(
select id,iParentId,vName,iLevel,iStatus from tb_Areas where id=@id
union all
select b.id,b.iParentId,b.vName,b.iLevel,b.iStatus
from cte A ,tb_Areas B where a.iParentId = b.id
)
select id,iParentId,vName,iLevel from cte  order by iLevel asc
END```

```WITH cte AS (
select ID,vName,iParentId ,CAST(vName AS VARCHAR(100)) AS fullname
from [tb_Areas]
WHERE iParentID=0 and ID=1
UNION ALL
SELECT a.id,a.vNAME,a.iParentID,CAST(b.fullname+'/'+a.vName AS VARCHAR(100)) AS FULLname
FROM [tb_Areas] a INNER JOIN cte b ON a.iParentID=b.id)
SELECT id,vname,fullname FROM cte```