频道栏目
首页 > 数据库 > 其他综合 > 正文
SQLSERVER实现递归
2017-04-05 09:25:26           
收藏   我要投稿

在开发过程中,我们经常遇到请求数据时,以及父节点查找所有子节点以及依据子节点查找所有父节点问题,其中最常用的就是依据某个省、查找该省下面的市、区、街道以及依据某个街道,查找到该街道所在区、市、省。
在此记录一下常用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
点击复制链接 与好友分享!回本站首页
上一篇:mysql光标的创建
下一篇:MySQL5.7.17安装教程
相关文章
图文推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站