求一个取得一棵树的所有孩子节点的sql文

yanxt

在工作中遇到一个问题,关于取得一棵树的所有孩子节点.
脑袋想破了,也没有想起解决方法.特来请教大侠们.
谢谢!

个表结构如下:
    id    | parent | flag
--------+-------- +--------
1011    101        5
1012    101        5
101      10          2
10        1            1
1                        7

想得到的结果如下:
1.当flag=5时,结果:
  oldid  |    id    | parent | flag
---------+--------+-------- +--------
1011           1011    101        5
1012           1012    101        5

2.当flag=2时,结果:

oldid    |    id    | parent | flag
---------+--------+-------- +--------
101        1011    101        5
101        1012    101        5
101        101      10          2


3.当flag=1时,结果:

oldid    |    id    | parent | flag
---------+--------+-------- +--------
10          1011    101        5
10          1012    101        5
10          101      10          2
10          10        1            1

也就是当指定flag时,
先查出对应的id,再以这个id去查询下面所有的子节点.
最后出来的数据,第一列数据,就是先查出来的id.


flag 最好用in的方式
如flag in ('1','3','5')

yanxt

建立测试表:
CREATE TABLE testtable
(
  id character varying(20) NOT NULL,
  parent character varying(20),
  flag character(1)
)
插入数据:
insert into testtable(id,parent,flag) values('1011','101','5');
insert into testtable(id,parent,flag) values('1012','101','5');
insert into testtable(id,parent,flag) values('101','10','2');
insert into testtable(id,parent,flag) values('10','1','1');
insert into testtable(id,parent,flag) values('1',null,'7');

tanfeng3

这个需要用 function 吧, 一个SQL估计不行。

yanxt

用function 或者 存储过程都可以.
唉!
对PostgreSQL 不熟,不会写!

在PostgreSQL8.4版本以后,
新增了递归功能.
如:
WITH RECURSIVE r AS (
       SELECT * FROM test WHERE flag in ('1','3','5')
     union   ALL
       SELECT test.* FROM test, r WHERE test.parent = r.id
     )
SELECT * FROM r ORDER BY id;

但是oldid如何加上去呢?

谢谢

crykun

这个我通常在程序中实现，用遍历或递归的方法，用到很多SQL,很笨的算法，如果单单依靠pg或者sql,是有点难度。不过楼上你既然想到了思路，多试试。
弄好了，公布一下，为这事，我十分苦恼，一直无好的算法。
会的朋友，支一下招。

yanxt

解决了.

第一步:
         建立一个游标.
         test_cursor cursor for
         selet *
         from testtable;
第二步:
         打开游标进行循环.
        LOOP
            FETCH test_cursor INTO test_ROW;
            EXIT WHEN NOT FOUND;
        if test_ROW.flag in ('1','3','5') then
               WITH RECURSIVE r AS (
                      SELECT * FROM test WHERE r.id =test_ROW.id
                     union   ALL
                      SELECT test.* FROM test, r WHERE test.parent = r.id
                    )
               SELECT test_ROW.id,* FROM r ORDER BY id;
        end if;
        END LOOP;
        CLOSE test_cursor ;

利用系统提供的功能,效率有很大的提高,代码也少了很多!

congli

学习!

congli

学习!!

renxiao2003

还是O强，直接就可以树出来。PG没有这样的功能吗？

yanxt

有.
如:
------------------------------------------------------------------------------------------------
               WITH RECURSIVE r AS (
                      SELECT * FROM test WHERE r.id =查询结点
                     union   ALL
                      SELECT * FROM test, r WHERE test.parent = r.id
                    )
               SELECT * FROM r ORDER BY id;
-------------------------------------------------------------------------------------------------
不知道有没有更好的方法?