关于递归取记录的死循环问题

冥王星 2009-12-05 05:00:48
数据库中有个部门级别表administrative_region
regionint是本部门标识号，通过dbo.f_getparentint(regionint)能得到上级部门的标识号,region是名称
现在想通过一个给定的regionint得到它所有的上级名称直到顶层：
如
给定2048，得到：
总部浙江大区杭州分部杭州江干区办事处A

这些是分几条记录存在administrative_region 表中
regionint region
1 总部
256 浙江大区
512 杭州分部
1024 杭州江干区
2048 办事处A

以下是我在查询分析器中写的功能草稿，但是计算到“杭州江干区”级别就取不出上级的ID了，只能取出本单位ID，造成死循环
declare @regioncode nvarchar(30),@finalregion nvarchar(100),@curregionNum bigint,@regionNum bigint,@tmp bigint
set @curregionNum=16843015
set @regioncode=N'china'
while(cast(@curregionNum as bigint)>5)
begin

select @regioncode=region,@regionNum=regionint from administrative_region where regionint=@curregionNum
set @tmp=dbo.f_getparentInt(@curregionNum)
set @curregionNum=@tmp
print @curregionNum
set @finalregion=cast(@regioncode as nvarchar)+cast(@finalregion as nvarchar)

end
select @finalregion
...全文
212 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
冥王星 2009-12-07
  • 打赏
  • 举报
 回复
谢谢，解决了，
是我的取上级区域的函数写错了。

CREATE   FUNCTION   [dbo].[f_getParentCode](   

@regionCode   nvarchar(15)

)RETURNS   nvarchar(15)

AS   

BEGIN   

DECLARE   @tmpStr nvarchar(20)

 

SET   @tmpstr=reverse(@regionCode)

while (left(@tmpstr,2)='0.')

  begin

   set  @tmpstr=stuff(@tmpstr,1,2,'')

  end

set @tmpstr=stuff(@tmpstr,1,charindex('.',@tmpstr),'')

if charindex('.',@regionCode )>0

  set @tmpstr=reverse(@tmpstr)

else

  set @tmpstr=N''

RETURN(@tmpstr)   

END  





declare @regioncode nvarchar(30),@region varchar(20),@finalregion nvarchar(100),@curregionNum bigint,@regionNum bigint,@tmp bigint ,@subregionNum bigint,@tmp1 bigint,@tmp2 bigint ,@tmp3 bigint ,@tmp4 bigint

set @subregionNum =16843015 

set @tmp1=0

set @finalregion=N''--@finalregion 必须要有个初始值，不然最终值会变成NULL

while(@tmp1<2) 

begin 

  select @region=region,@curregionNum=regionint ,@regioncode=region_code from administrative_region where regionint=@subregionNum 

  set @finalregion=cast(@region as nvarchar)+@finalregion

  set @regioncode=dbo.f_getparentcode(@regioncode)  

  if charindex('.',@regioncode)=0 --是否最高级别

     begin

       set @tmp1=@tmp1+1

    end

  set @subregionNum=dbo.f_code2int(@regioncode)



end

最终结果是：
总部浙江大区杭州分部杭州江干区办事处A (一条记录累加上级的名称直到最高级,并非是多个记录)
ChinaJiaBing 2009-12-05
  • 打赏
  • 举报
 回复 


--测试数据

CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))

INSERT tb SELECT '001',NULL ,'山东省'

UNION ALL SELECT '002','001','烟台市'

UNION ALL SELECT '004','002','招远市'

UNION ALL SELECT '003','001','青岛市'

UNION ALL SELECT '005',NULL ,'四会市'

UNION ALL SELECT '006','005','清远市'

UNION ALL SELECT '007','006','小分市'

GO



--查询指定节点及其所有子节点的函数

--2000

CREATE FUNCTION f_Cid(@PID char(3))

RETURNS @t_Level TABLE(PID char(3),Level int)

AS

BEGIN

	DECLARE @Level int

	SET @Level=1

	INSERT @t_Level SELECT @PID,@Level

	WHILE @@ROWCOUNT>0

	BEGIN

		SET @Level=@Level+1

		INSERT @t_Level SELECT a.PID,@Level

		FROM tb a,@t_Level b

		WHERE a.ID=b.PID

			AND b.Level=@Level-1

	END

	RETURN

END

GO



--调用函数查询002及其所有子节点

SELECT a.*

FROM tb a,f_Cid('002') b

WHERE a.ID=b.PID

go

--2005

;With china as

(

  select *,lev=1 from tb where id='002'

  union all 

  select b.*,lev=a.lev+1 from china a ,tb b where a.pid=b.id 

)

select * from china

go

drop table tb

go

drop function f_Cid





/*



(7 行受影响)

ID   PID  Name

---- ---- ----------

001  NULL 山东省

002  001  烟台市



(2 行受影响)



ID   PID  Name       lev

---- ---- ---------- -----------

002  001  烟台市        1

001  NULL 山东省        2



(2 行受影响)









*/
--小F-- 2009-12-05
  • 打赏
  • 举报
 回复 
答案石头写出来了 不过你可以看看这个

-->Title:Generating test data

-->Author:wufeng4552

-->Date :2009-09-30 08:52:38

set nocount on

if object_id('tb','U')is not null drop table tb

go

create table tb(ID int, ParentID int)

insert into tb select 1,0  

insert into tb select 2,1  

insert into tb select 3,1  

insert into tb select 4,2  

insert into tb select 5,3  

insert into tb select 6,5  

insert into tb select 7,6

-->Title:查找指定節點下的子結點

if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID

go

create function Uf_GetChildID(@ParentID int)

returns @t table(ID int)

as

begin

   insert @t select ID from tb where ParentID=@ParentID

   while @@rowcount<>0

   begin

      insert @t select a.ID from tb a inner join @t b

      on a.ParentID=b.id and 

      not exists(select 1 from @t where id=a.id)

   end 

return

end

go

select * from dbo.Uf_GetChildID(5)

/*

ID

-----------

6

7

*/

-->Title:查找指定節點的所有父結點

if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID

go

create function Uf_GetParentID(@ID int)

returns @t table(ParentID int)

as

begin

   insert @t select ParentID from tb where ID=@ID

   while @@rowcount!=0

   begin

     insert @t select a.ParentID from tb a inner join @t b

       on a.id=b.ParentID and 

       not exists(select 1 from @t where ParentID=a.ParentID)

   end

  return

end

go

select * from dbo.Uf_GetParentID(2)

/*

ParentID

-----------

1

0

*/
-狙击手- 2009-12-05
  • 打赏
  • 举报
 回复 


declare @regioncode nvarchar(30),@finalregion nvarchar(100),@curregionNum bigint,@regionNum bigint,@tmp bigint 

set @curregionNum=2048 

set @regioncode=N'china' 

set @finalregion = ''

while(cast(@curregionNum as bigint)>5) 

begin 

  

  select @regioncode=region,@regionNum=regionint 

	from administrative_region 

	where regionint=@curregionNum 

	select top 1 @tmp = regionint from administrative_region

    where regionint < @regionNum

    order by regionint desc

  

  set @curregionNum=@tmp 

  print @curregionNum 

  set @finalregion=cast(@regioncode as nvarchar)+cast(@finalregion as nvarchar) 



end 

select @finalregion



--Result:

/*



------------------------------------------------- 

浙江大区杭州分部杭州江干区办事处A



(1 行受影响)

*/

--End
-狙击手- 2009-12-05
  • 打赏
  • 举报
 回复 


declare @regioncode nvarchar(30),@finalregion nvarchar(100),@curregionNum bigint,@regionNum bigint,@tmp bigint 

set @curregionNum=2048 

set @regioncode=N'china' 

set @finalregion = ''

while(cast(@curregionNum as bigint)>5) 

begin 

  

  select @regioncode=region,@regionNum=regionint 

	from administrative_region 

	where regionint=@curregionNum 

	select top 1 @tmp = regionint from administrative_region

    where regionint < @regionNum

    order by regionint desc

  

  set @curregionNum=@tmp 

  print @curregionNum 

  set @finalregion=cast(@regioncode as nvarchar)+cast(@finalregion as nvarchar) 



end 

select @finalregion
-狙击手- 2009-12-05
  • 打赏
  • 举报
 回复
树哥
sgtzzc 2009-12-05
  • 打赏
  • 举报
 回复 
/*

标题：查询指定节点及其所有父节点的函数

作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 

时间：2008-05-12

地点：广东深圳

*/



create table tb(id varchar(3) , pid varchar(3) , name varchar(10))

insert into tb values('001' , null  , '广东省')

insert into tb values('002' , '001' , '广州市')

insert into tb values('003' , '001' , '深圳市')

insert into tb values('004' , '002' , '天河区')

insert into tb values('005' , '003' , '罗湖区')

insert into tb values('006' , '003' , '福田区')

insert into tb values('007' , '003' , '宝安区')

insert into tb values('008' , '007' , '西乡镇')

insert into tb values('009' , '007' , '龙华镇')

insert into tb values('010' , '007' , '松岗镇')

go



--查询指定节点及其所有父节点的函数

create function f_pid(@id varchar(3)) returns @t_level table(id varchar(3))

as

begin

  insert into @t_level select @id

  select @id = pid from tb where id = @id and pid is not null

  while @@ROWCOUNT > 0

  begin

    insert into @t_level select @id select @id = pid from tb where id = @id and pid is not null

  end

  return

end

go



--调用函数查询002(广州市)及其所有父节点

select a.* from tb a , f_pid('002') b where a.id = b.id order by a.id

/*

id   pid  name       

---- ---- ---------- 

001  NULL 广东省

002  001  广州市



（所影响的行数为 2 行）

*/



--调用函数查询003(深圳市)及其所有父节点

select a.* from tb a , f_pid('003') b where a.id = b.id order by a.id

/*

id   pid  name       

---- ---- ---------- 

001  NULL 广东省

003  001  深圳市



（所影响的行数为 2 行）

*/



--调用函数查询008(西乡镇)及其所有父节点

select a.* from tb a , f_pid('008') b where a.id = b.id order by a.id

/*

id   pid  name       

---- ---- ---------- 

001  NULL 广东省

003  001  深圳市

007  003  宝安区

008  007  西乡镇



（所影响的行数为 4 行）

*/



drop table tb

drop function f_pid
sgtzzc 2009-12-05
  • 打赏
  • 举报
 回复 
/*

标题：查询指定节点及其所有子节点的函数

作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 

时间：2008-05-12

地点：广东深圳

*/



create table tb(id varchar(3) , pid varchar(3) , name varchar(10))

insert into tb values('001' , null  , '广东省')

insert into tb values('002' , '001' , '广州市')

insert into tb values('003' , '001' , '深圳市')

insert into tb values('004' , '002' , '天河区')

insert into tb values('005' , '003' , '罗湖区')

insert into tb values('006' , '003' , '福田区')

insert into tb values('007' , '003' , '宝安区')

insert into tb values('008' , '007' , '西乡镇')

insert into tb values('009' , '007' , '龙华镇')

insert into tb values('010' , '007' , '松岗镇')

go



--查询指定节点及其所有子节点的函数

create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)

as

begin

  declare @level int

  set @level = 1

  insert into @t_level select @id , @level

  while @@ROWCOUNT > 0

  begin

    set @level = @level + 1

    insert into @t_level select a.id , @level

    from tb a , @t_Level b

    where a.pid = b.id and b.level = @level - 1

  end

  return

end

go



--调用函数查询001(广东省)及其所有子节点

select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id

/*

id   pid  name       

---- ---- ---------- 

001  NULL 广东省

002  001  广州市

003  001  深圳市

004  002  天河区

005  003  罗湖区

006  003  福田区

007  003  宝安区

008  007  西乡镇

009  007  龙华镇

010  007  松岗镇



（所影响的行数为 10 行）

*/



--调用函数查询002(广州市)及其所有子节点

select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id

/*

id   pid  name       

---- ---- ---------- 

002  001  广州市

004  002  天河区



（所影响的行数为 2 行）

*/



--调用函数查询003(深圳市)及其所有子节点

select a.* from tb a , f_cid('003') b where a.id = b.id order by a.id

/*

id   pid  name       

---- ---- ---------- 

003  001  深圳市

005  003  罗湖区

006  003  福田区

007  003  宝安区

008  007  西乡镇

009  007  龙华镇

010  007  松岗镇



（所影响的行数为 7 行）

*/



drop table tb

drop function f_cid
递归入门、递归遍历、递归穷举算法
本课程带你从递归算法基础入手，课程是精讲大量实际项目中常用到的案例，课程深入浅出，包括递归入门，递归遍历、弟归穷举算法及各种项目，适合算法爱好者一起学习，后附每堂课项目源码，感兴趣可以观注博客，不定期...
oracle递归查询出现死循环,在oracle中通过connect by prior来实现递归查询! - 无情的你，无情的......
发表于 @ 2007年02月16日　09:13:00 | 评论( 0 ) | 编辑| 举报| 收藏 旧一篇:使用weblogic连接池来得到数据库连接(通过配置文件进行读取的优化方案) | 新一篇:如何使用Rownum取某张表其中部分记录的某一条记录信息!...
mysql递归死循环查询_MySQL 递归查询实践总结
MySQL复杂查询使用实例By:授客QQ：1033553122表结构设计SELECT id, `name`, parent_id FROM `tb_testcase_suite`说明：parent_id值关联表自身id列的值，如果其值为-1，则表示该记录不存在父级记录，否则表示该记录...
java递归和循环
功能：求1-100的累加和 方案1：使用循环 ...1：递归尽头:什么时候不调用自己，如果没有头，将陷入死循环 常见的递归头：就是if判断 2：递归体 什么时候需要调用自身方法。 注意：就...
循环和递归可以相互替代吗？
有些递归只需要一个循环就可以替代，而有些递归的改写需要循环+栈，即要利用一定的辅助空间记录过程中的某些数据才可以。那么什么样的递归只需要一个循环，什么样的递归需要循环+栈呢？这个问题是本篇文章将要解决的...
疑难问题

22,207

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章