SQL SERVER实现递归

在开发过程中，我们经常遇到请求数据时，以及父节点查找所有子节点以及依据子节点查找所有父节点问题，其中最常用的就是依据某个省、查找该省下面的市、区、街道以及依据某个街道，查找到该街道所在区、市、省。 在此记录一下常用SQL语句；数据库设计如下： 一、由于在SQL SERVER2000中不支持递归，所以我们需要使用函数(表值函数)进行查询 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)

显示结果：

二、在SQL SERVER 2005以上，直接使用递归就可以了，不需要在定义函数了， 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

对于SQL SERVER 2005以及以上的SQL SERVER版本，调用的话，可以直接使用存储过程，如果使用SQL 语句的话，可以直接将BEGIN/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