寻求递归sql语句

Question

寻找sql语句：有如下数据：
id   pid
1     3
3     5
5     10
要求得到数据：
id tmpid
1    1
1    3
1    5
1    10
3    3
3    5
3    10
5    5
5    10
10   10就是对每一个数据，都将它的所有上级都按照层次依次显示出来。    

Answer 1

思路是这样的：
1.首先建立关系表：（这里好像是4级：如过更多道理一样：）
SELECT Id, Pid, Id2, (SELECT Pid FROM Test d WHERE d.Id = c.Id2) Id3
  FROM (SELECT Id, Pid, (SELECT b.Pid FROM Test b WHERE b.Id = a.Pid) Id2
          FROM Test a) c;
2.其次是把所有的部门跟部门上级关联，然后根据关系表过滤满足条件的关系，最终结果就是下面这样的：
SELECT Id, Mtpid
  FROM (SELECT Id, Mtpid
          FROM (SELECT Id FROM Test), (SELECT Pid Mtpid FROM Test)
        UNION
        SELECT Id, Id Mtpid FROM Test) a
 WHERE a.Mtpid =
       (SELECT Id
          FROM (SELECT Id,
                       Pid,
                       Id2,
                       (SELECT Pid FROM Test d WHERE d.Id = c.Id2) Id3
                  FROM (SELECT Id,
                               Pid,
                               (SELECT b.Pid FROM Test b WHERE b.Id = a.Pid) Id2
                          FROM Test a) c) d
         WHERE d.Id = a.Id)
    OR a.Mtpid =
       (SELECT Pid
          FROM (SELECT Id,
                       Pid,
                       Id2,
                       (SELECT Pid FROM Test d WHERE d.Id = c.Id2) Id3
                  FROM (SELECT Id,
                               Pid,
                               (SELECT b.Pid FROM Test b WHERE b.Id = a.Pid) Id2
                          FROM Test a) c) d
         WHERE d.Id = a.Id)
    OR a.Mtpid =
       (SELECT Id2
          FROM (SELECT ID,
                       Pid,
                       Id2,
                       (SELECT Pid FROM Test d WHERE d.Id = c.Id2) Id3
                  FROM (SELECT Id,
                               Pid,
                               (SELECT b.Pid FROM Test b WHERE b.Id = a.Pid) Id2
                          FROM Test a) c) d
         WHERE d.Id = a.Id)
    OR a.Mtpid =
       (SELECT Id3
          FROM (SELECT ID,
                       Pid,
                       Id2,
                       (SELECT Pid FROM Test d WHERE d.Id = c.Id2) Id3
                  FROM (SELECT Id,
                               Pid,
                               (SELECT b.Pid FROM Test b WHERE b.Id = a.Pid) Id2
                          FROM Test a) c) d
         WHERE d.Id = a.ID);

Answer 2

对上述描述的补充：我是在建立了这样一张表的情况下测试的：
create table TEST
(
  ID  NUMBER(3),
  PID NUMBER(3)
);

Answer 3

with tt as(select 1 id,3 pid from dual
  union all select 3,5 from dual
  union all select 5,10 from dual)
  
select a.id,b.pid
from (select id from tt union select pid from tt) a,
  (select * from tt 
   union all select id,id from tt
   union select pid,pid from tt) b
start with a.id=b.pid and b.id=b.pid
connect by prior b.pid=b.id
  and prior a.id=a.id
  and prior b.pid<>b.pidID PID
1 1
1 3
1 5
1 10
3 3
3 5
3 10
5 5
5 10
10 10

Answer 4

SQL>insert into test_recursive values(1,3);
SQL>insert into test_recursive values(3,5);
SQL>insert into test_recursive values(5,10);这是不包含本身的结果select first_value(id) over(partition by part order by level) id, pid
  from (select level, id, pid,(ROWNUM - LEVEL) part
          from test_recursive
        connect by prior pid = id)
   ID   PID
----- -----
    1     3
    1     5
    1    10
    3     5
    3    10
    5    10

Answer 5

with t as(select id from tt union
  select pid from tt)
   
select a.id,b.pid
  from t a,
    (select * from tt union all select id,id from t) b
  
start with a.id=b.pid and b.id=b.pid
connect by prior b.pid=b.id
       and prior a.id=a.id
       and prior b.pid<>b.pidID PID
1 1
1 3
1 5
1 10
3 3
3 5
3 10
5 5
5 10
10 10

Answer 6

sql小王子好厉害。下面这个join的方式，有没有可能对性能有影响

Answer 7

这个为什么不改进为
select id, pid,CONNECT_BY_ROOT ID
          from test_recursive
        connect by prior pid = id