serial newserial stcd dat A B C 20051201 --B替换A B F C 20051204 --F替换B F H C 20051205 --H替换F H 0 20051207 A->B->F->H只有输入这个串的任何一个serial,就能找到所有
create table c_out(serial varchar(20), newserial varchar(20), stcd varchar(20), dat varchar(20)) insert into c_out select 'A', 'B', 'C', '20051201' union All select 'C', null, '0', '20051202' union All select 'D', 'M', 'C', '20051202' union All select 'B', 'F', 'C', '20051204' union All select 'F', 'H' , 'C', '20051205' union All select 'H', null, '0', '20051207' union All select 'M', 'D' , 'C' , '20051209' union All select 'D', null, '0' , '20051215' go create proc GetPath(@p varchar(20)) as begin create table #b (path varchar(20)) declare @S varchar(20) set @s=@p insert into #b select serial from c_out where serial=@S and stcd='0' while 1=1 begin select @S=newserial from c_out where serial=@S and newserial not in (select * from #b) if @@rowcount=0 set @S=null if not @s is null insert into #b select @s else BREAK end set @s=@p while 1=1 begin print @s select @S=serial from c_out where newserial=@S and serial not in (select * from #b) if @@rowcount=0 set @S=null if not @s is null insert into #b select @s else break end select * from #b end Go GetPath 'D' drop table c_out drop proc GetPath
A B C 20051201 --B替换A
B F C 20051204 --F替换B
F H C 20051205 --H替换F
H 0 20051207
A->B->F->H只有输入这个串的任何一个serial,就能找到所有
create table c_out(serial varchar(20), newserial varchar(20), stcd varchar(20), dat varchar(20))
insert into c_out
select
'A', 'B', 'C', '20051201'
union All select
'C', null, '0', '20051202'
union All select
'D', 'M', 'C', '20051202'
union All select
'B', 'F', 'C', '20051204'
union All select
'F', 'H' , 'C', '20051205'
union All select
'H', null, '0', '20051207'
union All select
'M', 'D' , 'C' , '20051209'
union All select
'D', null, '0' , '20051215'
go
create proc GetPath(@p varchar(20))
as
begin
create table #b (path varchar(20))
declare @S varchar(20)
set @s=@p
insert into #b select serial from c_out where serial=@S and stcd='0'
while 1=1
begin
select @S=newserial from c_out where serial=@S and newserial not in (select * from #b)
if @@rowcount=0
set @S=null
if not @s is null
insert into #b select @s
else
BREAK
end
set @s=@p
while 1=1
begin
print @s
select @S=serial from c_out where newserial=@S and serial not in (select * from #b)
if @@rowcount=0
set @S=null
if not @s is null
insert into #b select @s
else
break
end
select * from #b
end
Go
GetPath 'D'
drop table c_out
drop proc GetPath