求一个递归查询的写法

[hgamezoom]

表结构：

datacode, dataname, parentcode
1 AAA 0
2 BBB 1
3 CCC 2


现在我要取得如下的结果：


datacode, dataname, parentcode， rootcode
1 AAA 0 0
2 BBB 1 1
3 CCC 2 1

就是增加一列根id，一直追溯到不为0的顶级父id为止。求sql。

[sumury]

select a.datacode,
a.dataname,
a.parentcode,
(select min(parentcode)
from <table> b
where parentcode <> 0
start with b.datacode = a.datacode
connect by b.datacode = prior b.parentcode) rootcode
from <table> a

[hupeng213]

select a.*,
(select replace(wm_concat(distinct parentcode), ',', '-->')
from ((select 1 as dataCode, 'AAA' as dataName, 0 as parentCode
from dual
union
select 2, 'BBB', 1
from dual
union
select 3, 'CCC', 2 from dual)) b
start with b.dataCode = a.dataCode
connect by b.dataCode = prior b.parentCode) as path
from (select 1 as dataCode, 'AAA' as dataName, 0 as parentCode
from dual
union
select 2, 'BBB', 1
from dual
union
select 3, 'CCC', 2 from dual) a

--结果
1 1 AAA 0 0
2 2 BBB 1 0-->1
3 3 CCC 2 0-->1-->2

[xpingping]

试试吧……

with tb as
(select 1 datacode,'AAA'dataname, 0 parentcode from dual union all
select 2 ,'BBB', 1 from dual union all
select 3 ,'CCC', 2 from dual) --临时表
select tb.*,
decode(connect_by_root parentcode,parentcode,0,1) -- 根节点，然后和父节点比较
from tb 
start with parentcode=0 --从0开始，即根节点0
connect by prior datacode=parentcode

[wmg494005678]

Connect By Prior

[Larry]

WITH TB(DATACODE,DATANAME,PARENTCODE) AS
(
  SELECT 1,'AAA',0 FROM DUAL UNION
  SELECT 2,'BBB',1 FROM DUAL UNION
  SELECT 3,'CCC',2 FROM DUAL UNION
  SELECT 4,'DDD',0 FROM DUAL UNION
  SELECT 5,'EEE',4 FROM DUAL 
)
,TW(DATACODE) AS
(
  SELECT DATACODE FROM TB WHERE PARENTCODE = 0
)
SELECT TB.*,PARENTCODE ROOTCODE FROM TB WHERE PARENTCODE = 0 UNION
SELECT DISTINCT TB.*,CONNECT_BY_ROOT(PARENTCODE) ROOTCODE FROM TB,TW
START WITH (PARENTCODE = TW.DATACODE)
CONNECT BY PRIOR TB.DATACODE =TB.PARENTCODE