以下是oracle的写法
------------建表-----------------------------
CREATE TABLE TBL_TEST
(
ID NUMBER,
NAME VARCHAR2(100),
PID NUMBER DEFAULT 0
)--------------------------------------------
insert into TBL_TEST values(0,'開発',0);
insert into TBL_TEST values(1,'VC',0);
insert into TBL_TEST values(2,'VC-1',1);
insert into TBL_TEST values(3,'VC-1-0',2);
insert into TBL_TEST values(4,'VC-1-1',2);
insert into TBL_TEST values(5,'VC-2',1);
insert into TBL_TEST values(6,'VC-2-0',5);
insert into TBL_TEST values(7,'VC-2-1',5);
insert into TBL_TEST values(8,'JAVA',0);
insert into TBL_TEST values(9,'JAVA-1',8);
insert into TBL_TEST values(10,'JAVA-2',8);
insert into TBL_TEST values(11,'JAVA-3',8);
insert into TBL_TEST values(12,'JAVA-4',8);
---------------------------------------------
select t.* from TBL_TEST t
connect by prior t.id = t.pid
start with t.pid=1
请问像这种方法在postgresql中有没有?有的话请提供。谢谢。
------------建表-----------------------------
CREATE TABLE TBL_TEST
(
ID NUMBER,
NAME VARCHAR2(100),
PID NUMBER DEFAULT 0
)--------------------------------------------
insert into TBL_TEST values(0,'開発',0);
insert into TBL_TEST values(1,'VC',0);
insert into TBL_TEST values(2,'VC-1',1);
insert into TBL_TEST values(3,'VC-1-0',2);
insert into TBL_TEST values(4,'VC-1-1',2);
insert into TBL_TEST values(5,'VC-2',1);
insert into TBL_TEST values(6,'VC-2-0',5);
insert into TBL_TEST values(7,'VC-2-1',5);
insert into TBL_TEST values(8,'JAVA',0);
insert into TBL_TEST values(9,'JAVA-1',8);
insert into TBL_TEST values(10,'JAVA-2',8);
insert into TBL_TEST values(11,'JAVA-3',8);
insert into TBL_TEST values(12,'JAVA-4',8);
---------------------------------------------
select t.* from TBL_TEST t
connect by prior t.id = t.pid
start with t.pid=1
请问像这种方法在postgresql中有没有?有的话请提供。谢谢。
使用如下:
iihero=# create table tbl_test(id int, name varchar(100), pid int);
CREATE TABLE
iihero=# insert into TBL_TEST values(1,'VC',0);
INSERT 0 1
iihero=# insert into TBL_TEST values(2,'VC-1',1);
INSERT 0 1
iihero=# insert into TBL_TEST values(3,'VC-1-0',2);
INSERT 0 1
iihero=# insert into TBL_TEST values(4,'VC-1-1',2);
INSERT 0 1
iihero=# insert into TBL_TEST values(5,'VC-2',1);
INSERT 0 1
iihero=# insert into TBL_TEST values(6,'VC-2-0',5);
INSERT 0 1
iihero=# insert into TBL_TEST values(7,'VC-2-1',5);
INSERT 0 1
iihero=# insert into TBL_TEST values(8,'JAVA',0);
INSERT 0 1
iihero=# insert into TBL_TEST values(9,'JAVA-1',8);
INSERT 0 1
iihero=# insert into TBL_TEST values(10,'JAVA-2',8);
INSERT 0 1
iihero=# insert into TBL_TEST values(11,'JAVA-3',8);
INSERT 0 1
iihero=# insert into TBL_TEST values(12,'JAVA-4',8);
INSERT 0 1
iihero=# with recursive r as (select * from tbl_test where pid = 1 union all select tbl_test.*
from tbl_test, r where tbl_test.pid = r.id)
iihero-# select * from r order by pid;
id | name | pid
----+--------+-----
2 | VC-1 | 1
5 | VC-2 | 1
3 | VC-1-0 | 2
4 | VC-1-1 | 2
6 | VC-2-0 | 5
7 | VC-2-1 | 5
(6 rows)
看看是不是你想要的。