数据库有一张表nodeinfor,其中有3个字段:nodeno(本地网点编码)、upnodeno(上级网点编码)、nodetree(网点树)。nodeno和upnodeno都有值,nodetree为空。需要写一个存储过程,使nodetree的值为上级网点编码+本地网点编码的组合。如果上级网点编码还有上级网点编码,也需要一起组合,直到最顶层网点编码为000000。形成一个树状结构。例如:
nodeno upnodeno nodetree
270300 270000 null
270000 000000 null
340300 340000 null
340000 000000 null
340301 340300 null
需要存储过程实现如下:
nodeno upnodeno nodetree
270300 270000 000000270000270300
270000 000000 000000270000
340300 340000 000000340000340300
340000 000000 000000340000
340301 340300 000000340000340300340301
... ... ...
也就是说:nodetree=......+上上级网点编码+上级网点编码+本地网点编码
在线求高手帮忙写一个,非常感谢。可能需要写一个递归函数。
nodeno upnodeno nodetree
270300 270000 null
270000 000000 null
340300 340000 null
340000 000000 null
340301 340300 null
需要存储过程实现如下:
nodeno upnodeno nodetree
270300 270000 000000270000270300
270000 000000 000000270000
340300 340000 000000340000340300
340000 000000 000000340000
340301 340300 000000340000340300340301
... ... ...
也就是说:nodetree=......+上上级网点编码+上级网点编码+本地网点编码
在线求高手帮忙写一个,非常感谢。可能需要写一个递归函数。
insert into node(nodeno,upnodeno)
select '270300','270000'
union all
select '270000','000000'
union all
select '340300','340000'
union all
select '340000','000000'
union all
select '340301','340300'create function node_f (@tree2 varchar(500))
RETURNS varchar(500)
as
begin
declare @tree varchar(500),@tree3 varchar(500)
set @tree=@tree2
set @tree3=@tree2
while @tree2<>'000000'
begin
select @tree2=upnodeno From node
where nodeno=@tree2
set @tree3=@tree2+@tree3
end
RETURN @tree3
endselect nodeno,upnodeno,dbo.node_f(nodeno) From node
2 FROM node
3 START WITH UpNodeNo = '000000'
4 CONNECT BY PRIOR NodeNo = UpNodeNo;NODENO UPNODENO TREENODE
---------- ---------- --------------------------------------------------------------------------------
270000 000000 000000270000
270300 270000 000000270000270300
340000 000000 000000340000
340300 340000 000000340000340300
340301 340300 000000340000340300340301
set nodetree = (select replace('000000'||SYS_CONNECT_BY_PATH(NodeNo, '\'),'\')
FROM node
START WITH UpNodeNo = '000000'
CONNECT BY PRIOR NodeNo = UpNodeNo)
如果需要用存储的话将这段SQL写在存储里就行了。注需要oracle9以上版本。(呵呵!剽窃了楼上的。)
set nodetree = (select replace('000000'||SYS_CONNECT_BY_PATH(NodeNo, '\'),'\')
FROM node
START WITH UpNodeNo = '000000'
CONNECT BY PRIOR NodeNo = UpNodeNo)
写错!
==
update node t1
set nodetree =(select t2.tree
from
(select NODENO,
replace('000000'||SYS_CONNECT_BY_PATH(NodeNo, '\'),'\') tree
FROM node
START WITH UpNodeNo = '000000'
CONNECT BY PRIOR NodeNo = UpNodeNo
) t2
where t2.NODENO = t1.NODENO
)