请问这样的SQL怎么写？

Question

表A
 col   parent
 11      1
 111     11
 1111    111
 12      1
 121     12
 1212    121
 12121   1212
我想得到的结果是
表B
 col1  col2
 11     1
 111    11
 111    1
 1111   111
 1111   11
 1111   1
 12     1
 121    12
 121    1
 1212   121
 1212   12
 1212   1
 12121  1212
 12121  121
 12121  12
 12121  1
也就是得到每列的所有祖先的值，应该不是很难，可惜我还没想出来怎么写这个SQL
请大家帮忙了，谢谢。

Answer 1

这里面有递归，不知道怎么搞，不过可以写个 Java 类放到 Oracle JVM 中运行，
就像一个存储过程。 Oracle 站点上有个 Viewable CD 下载，上面有很多 PDFs.里面讲到怎么建立 
Java 类型，就像函数或过程一样运行。你打开 Enterprise management Console 在一个 Schema 中创建一个 Java Source 类型对象，然后写个Java 代码。

Answer 2

用SQL能不能解决？
请高手帮个忙！

Answer 3

SQL> select * from t;        ID        PID
---------- ----------
        11        111
       111       1111
         1         11
SQL> select * from t start with pid in(select pid from t) connect by prior id=pid;        ID        PID
---------- ----------
         1         11
        11        111
         1         11
       111       1111
        11        111
         1         116 rows selected.

Answer 4

to：wfeng7907(无风)
你误解我的意思le，我需要的结果是
        ID        PID
---------- ----------
         1         11
        11        111
         1        111
       111       1111
        11       1111
         1       1111

Answer 5

select b.id,a.pid from tree a ,tree b where a.pid like b.id || '%' and a.pid <> b.id

Answer 6

to： xiahare(软件人)
按照您这样的语句写出来得到的也不是我想要的格式
您再看看帖子的要求。
这张表有几万条数据，而且col的parent不一定有几级，有可能是一级(加本身1个parent)，也可能是9级（加本身9个parent）

Answer 7

SQL> select * from test;       COL     PARENT
---------- ----------
        11          1
       111         11
      1111        111
        12          1
       121         12
      1212        121
     12121       1212已选择7行。SQL> select distinct a.col,b.parent
  2  from test a,test b
  3  where a.col like b.parent||'%' and a.col<>b.parent;       COL     PARENT
---------- ----------
        11          1
        12          1
       111          1
       111         11
       121          1
       121         12
      1111          1
      1111         11
      1111        111
      1212          1
      1212         12
      1212        121
     12121          1
     12121         12
     12121        121
     12121       1212已选择16行。

Answer 8

zmgowin(hermit)的方法是假设下级ID是上级ID加上一个值，如果ID没有这种关系，那么可以写个函数，输入两个ID，返回1表示这两个ID是有上下级关系，0表示没有关系。这样可以
select a.id,b.id from test a, test b where myfun(a.id, b.id)=1;create or replace function myfun(id1 varchar2, id2 varchar2) return number
as
  id3  varchar2(40);
  v_ret number;
begin
  select count(*) into v_ret from test where COL=id1 and PARENT=id2;
  if v_ret=0 then
    select count(*) into v_ret from test where COL=id1;
    if v_ret=1 then
      select PARENT into id3 from from test where COL=id1;
      v_ret:=myfun(id3, id2);
    end if;
  end if;
  return v_ret;
end;
/

Answer 9

to:bobfang(匆匆过客)
您说的对，ID编号并没有这样的关系，他们的层次关系是通过ID的PARENT联接的
我试试您的方法，马上结贴

Answer 10

按照bobfang(匆匆过客)的方法，如果一个ID对应一个PARENT，结果就是正确的。
但是，一个ID可能有不至有一个PARENT，另外ID本身也是自己的PARENT
请大家再帮忙看看

Answer 11

select substr(col1,2,instr(col1,'/',2)-2) col1,parent col2 from 
(select SYS_CONNECT_BY_PATH(COL,'/')||'/' col1,col,parent,level from a a1 connect by  a1.col= prior a1.parent);SQL> @d:\test.sqlCOL1           COL2
-------------- ----------
11             1
111            11
111            1
1111           111
1111           11
1111           1
12             1
121            12
121            1
1212           121
1212           12COL1           COL2
-------------- ----------
1212           1
12121          1212
12121          121
12121          12
12121          1已选择16行。上面是在9i下测试的，10G的好像有个CONNECT_BY_ROOT 的方法，会更简单些

Answer 12

其实你的表结构很适合用connect by start with不错！

Answer 13

请 liuyi8903(风继续吹)明示。

Answer 14

再简单描述一下我需要的功能
表A
LOCA        PARENT
A           B
B           C
C           D
D           E
E           F
表B
LOCA        ANCESTOR
A           A
A           B
A           C
A           D
A           E
A           F
B           B
B           C
B           D
B           E
B           F
C           C
C           D
C           E
C           F
D           D
D           E
D           F
E           E
E           F
F           F
谢谢大家帮忙，
我用的是oracle817，A表有6万条数据，因为层次比较深，估计完整的算出来，B表里会生成40万左右的数据
duanzilin(寻)的方法我没有看懂，执行也出错
我又给帖子加了50分，解决了问题就结贴

Answer 15

SYS_CONNECT_BY_PATH方法是9i新引进的，9i以下版本实现起来会比较麻烦

Answer 16

817是不是已经有分析函数了，试试下面的方法：
 select first_value(id) over(partition by part order by lev) id,parent
 from
 (select id,parent,lev,(rownum - lev) part from
       (select id,parent,level lev from t1 connect by id = prior parent) 
 )SQL> 
  6  /ID         PARENT
---------- ----------
a          b
a          c
a          d
a          e
a          f
b          c
b          d
b          e
b          f
c          d
c          e
c          f
d          e
d          f
e          f15 rows selected这里有个问题，id的父结点不可能是它本身，如果是它本身connect by就会出错，所以这里不能列出像你给出的祖先是自己的情况，但是对于你给的第一个例子却是正确的SQL> /ID         PARENT
---------- ----------
11         1
111        11
111        1
1111       111
1111       11
1111       1
12         1
121        12
121        1
1212       121
1212       12
1212       1
12121      1212
12121      121
12121      12
12121      116 rows selected

Answer 17

如果你的ORACLE版本不支持分析函数，我想就比较难办了，还是写个函数吧
顺便指出这里level和rownum不是表字段，是oracle的伪列，sql如下：
select first_value(LOCA) over(partition by part order by lev) id,PARENT as ANCESTOR
 from
 (select LOCA,PARENT,lev,(rownum - lev) part from
       (select LOCA,PARENT,level lev from A connect by LOCA = prior PARENT) 
 )

Answer 18

to：duanzilin(寻)
817里有你用到的这些分析函数，我测试了一下，上述方法是可用的
因为数据量比较大，我今晚在服务器上运行一下
谢谢。

Answer 19

按照duanzilin(寻)的方法问题得到解决
6万条数据，经过1.4小时，生成44万条结果数据
谢谢了