CONNECT BY 超难问题！在线等....高手请进！

Question

CREATE TABLE NEW_TABLE
AS SELECT treenodename,treenodeid,parenttreenodeid,(treenodeid || parenttreenodeid) fullcode FROM OLD_TABLE;然后删除旧表,把新报名改为旧表名,OK?

Answer 1

...所有...父节点＋本身treenodeid !!!!看来我是搞错了,哈哈!!!再来一个如果是以treenodename作为主键:
CREATE TABLE NEW_TABLE
AS SELECT A.treenodename,A.treenodeid,A.parenttreenodeid,(SELECT A.treenodeid || B.parenttreenodeid FROM OLD_TABLE B WHERE B.treenodename=A.treenodename) fullcode FROM OLD_TABLE A;应该没问题了吧

Answer 2

如果是动态的可以使用视图！方式同上！！！！只是create view!

Answer 3

没有一条SQL语句可以解决，如果你发现了方法，麻烦告诉我
目前类似问题的解决方案是存储过程

Answer 4

一条SQL语句可以解决，如果你发现了方法告诉我

Answer 5

难道不能用一条sql实现吗?嵌套也不行?

Answer 6

SQL> select * from t1;TREENODENAME         TREENODEID PARENTTREENODEID
-------------------- ---------- ----------------
总公司                     4567
上海分公司                 4568             4567
浙江分公司                 4569             4567
电子开发事业部             4570             4569
房地产开发部               4571             4569SQL> select
  2    treenodename,treenodeid,parenttreenodeid,
  3    (select sum(power(10000,level-(select max(level)
  4  from t1 start with treenodeid=a.treenodeid connect by prior parenttreenodei
d=4570))*treenodeid) fullcode from t1 start with treenodeid=a.treenodeid connect
 by prior parenttreenodeid=treenodeid) fullcode
  5  from
  6    t1 a;TREENODENAME         TREENODEID PARENTTREENODEID        FULLCODE
-------------------- ---------- ---------------- ---------------
总公司                     4567                             4567
上海分公司                 4568             4567        45674568
浙江分公司                 4569             4567        45674569
电子开发事业部             4570             4569    456745694570
房地产开发部               4571             4569    456745694571

Answer 7

Select Treenodename,
       Treenodeid,
       Parenttreenodeid,
       Replace(Sys_Connect_By_Path(Treenodeid, '/'), '/', '') Fullcode
  From Test
Connect By Prior Treenodeid = Parenttreenodeid
 Start With Parenttreenodeid Is Null；TREENODENAME         TREENODEID PARENTTREENODEID FULLCODE
-------------------- ---------- ---------------- -------------
总公司               4567                        4567
上海分公司           4568       4567             45674568
浙江分公司           4569       4567             45674569
电子开发事业部       4570       4569             456745694570
房地产开发部         4570       4569             456745694570