关于嵌套循环~~~~~问题 帮帮忙 3Q~~~~

[tojht]

表 id name fullname
101 a null
102 b null
10201 c b_c
1020101 d b_c_d
1020102 e b_c_e
102010102 f b_c_d_f


有没有好的算法呀 。。。 让他的FULLNAME 显示为上级编号的名称加上下一级的编号名称



DECLARE --定义变量

@oneName varchar(100), --第一级名称
@oneID varchar(100), --第一级编号
@twoName varchar(100), --第二级名称
@twoID varchar(100), --第二级编号
@threeID varchar(100),
@threeName varchar(100),
@fourID varchar(100),
@fourName varchar(100)
DECLARE A1 CURSOR FOR --定义游标
select ACCCODE,ACCNAME from ALY_ACCSUM --查询所有编号小于3位的编号和名称
where len(ACCCODE)<=3
/*处理循环开始*/

--set @str_path2='D:\教育分中心数据\原始数据\'
--set @str_path='D:\教育分中心数据\'


OPEN A1 --打开游标

FETCH NEXT FROM A1 into @oneID,@oneName --把数据填充到第一级名称和编号变量里

WHILE @@FETCH_STATUS = 0 --循环 如果还有数据的话
BEGIN

declare B1 cursor for
select ACCCODE,ACCNAME from ALY_ACCSUM
where len(ACCCODE)>3 and len(ACCCODE)<=5
and subString(ACCCODE,0,4)=@oneID
--@oneName
--select @twoID=subString(kmdm,0,4),@twoName=kmmc --查询截取前3位的二级编号和名称 放入变量里
--from hz where len(kmdm)>3 and len(kmdm)<=5

Open B1
Fetch Next from B1 into @twoID,@twoName

if @@fetch_status<>0
print NULL
while @@FETCH_STATUS = 0

begin
update ALY_ACCSUM set ACCFULLNAME=@oneName+'_'+@twoName where ACCCODE=@twoID --那么 就把二级的名称改为第一级名称+_+第二级名称

declare C1 cursor for
select ACCCODE,ACCNAME from ALY_ACCSUM
where len(ACCCODE)>5 and len(ACCCODE)<=7
and subString(ACCCODE,0,6)=@twoID

Open C1
Fetch Next from C1 into @threeID,@threeName

if @@fetch_status<>0
print NULL
while @@FETCH_STATUS = 0

begin
update ALY_ACCSUM set ACCFULLNAME=@oneName+'_'+@twoName+'_'+@threeName where ACCCODE=@threeID

declare D1 cursor for
select ACCCODE,ACCNAME from ALY_ACCSUM
where len(ACCCODE)>7 and len(ACCCODE)<=9
and subString(ACCCODE,0,8)=@threeID

Open D1
Fetch Next from D1 into @fourID,@fourName

if @@fetch_status<>0
print NULL
while @@FETCH_STATUS = 0

begin
update ALY_ACCSUM set ACCFULLNAME=@oneName+'_'+@twoName+'_'+@threeName+'_'+@fourName where ACCCODE=@fourID
fetch next from D1 into @fourID,@fourName
END
close D1
deallocate D1









fetch next from C1 into @threeID,@threeName
END
close C1
deallocate C1

Fetch Next from B1 into @twoID,@twoName
end


close B1
deallocate B1


--EXEC(@strSql2)

-- 调试
--select @strSql
-- break


FETCH NEXT FROM A1 into @oneID,@oneName --继续填充
END

CLOSE A1 --关闭游标
DEALLOCATE A1


我是这么写的 很笨 ，， 而且超级慢 20万条的数据要耗时很久 谢谢各位大大帮帮忙。。。

[华夏小卒]

if object_id('tb')is not null drop table tb
go
create table tb(id varchar(10),name char(1),fullname varchar(10))
insert tb select
'101'         ,       'a',            null union all select
'102'         ,       'b',            null union all select
'10201'       ,       'c',            null union all select
'1020101'     ,       'd',            null union all select
'1020102'     ,       'e',            null union all select
'102010102'   ,       'f' ,           null


declare @maxLen int,@minLen int

select @maxLen=max(len(id)),@minLen=min(len(id)) from tb 

while @minLen+2<=@maxLen
begin
	update b
	set fullname=isnull(a.fullname,a.name)+'-'+b.name
	from tb a,tb b
	where len(a.id)=@minLen and len(b.id)=@minLen+2
		  and charindex(a.id,b.id)>0
	set @minLen=@minLen+2
end



select * from tb 

id         name fullname
---------- ---- ----------
101        a    NULL
102        b    NULL
10201      c    b-c
1020101    d    b-c-d
1020102    e    b-c-e
102010102  f    b-c-d-f

(6 行受影响)

[tojht]

谢谢。。。~