informix 中層次/樹型/Hierarchical查詢的使用

參考：

[@more@]

CREATE TABLE employee(
empid INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(10),
salary DECIMAL(9, 2),
mgrid INTEGER
);
INSERT INTO employee VALUES ( 1, 'Jones', 30000, 10);
INSERT INTO employee VALUES ( 2, 'Hall', 35000, 10);
INSERT INTO employee VALUES ( 3, 'Kim', 40000, 10);
INSERT INTO employee VALUES ( 4, 'Lindsay', 38000, 10);
INSERT INTO employee VALUES ( 5, 'McKeough', 42000, 11);
INSERT INTO employee VALUES ( 6, 'Barnes', 41000, 11);
INSERT INTO employee VALUES ( 7, 'O''Neil', 36000, 12);
INSERT INTO employee VALUES ( 8, 'Smith', 34000, 12);
INSERT INTO employee VALUES ( 9, 'Shoeman', 33000, 12);
INSERT INTO employee VALUES (10, 'Monroe', 50000, 15);
INSERT INTO employee VALUES (11, 'Zander', 52000, 16);
INSERT INTO employee VALUES (12, 'Henry', 51000, 16);
INSERT INTO employee VALUES (13, 'Aaron', 54000, 15);
INSERT INTO employee VALUES (14, 'Scott', 53000, 16);
INSERT INTO employee VALUES (15, 'Mills', 70000, 17);
INSERT INTO employee VALUES (16, 'Goyal', 80000, 17);
INSERT INTO employee VALUES (17, 'Urbassek', 95000, NULL);

一、 基本操作
select empid,name,mgrid
from employee
start with name = 'Goyal' connect by prior empid=mgrid
CONNECT_BY_ROOT

二、 CONNECT_BY_ROOT 一元運算子（unary operator）
該一元運算子計算每個節點的根節點對應的表示式的值
用法：
CONNECT_BY_ROOT
比較希望得到每個節點的根節點對應僱員的工資是否超過70000，如果是，則顯示1
select empid,name,mgrid,abs(CONNECT_BY_ROOT salary-70000)/(CONNECT_BY_ROOT salary-70000)
from employee
start with name = 'Goyal' connect by prior empid=mgrid;
或者當成函式來用
select empid,name,mgrid,abs(CONNECT_BY_ROOT(salary)-70000)/(CONNECT_BY_ROOT(salary)-70000)
from employee
start with name = 'Goyal' connect by prior empid=mgrid;

三、 PRIOR 運算子
指明父子結點關聯的條件

四、 LEVEL 偽列（pseudocolumn）
該偽列指明樹查詢中當前結點的樹高

select empid,name,mgrid,level
from employee
start with name = 'Goyal' connect by prior empid=mgrid;

五、 CONNECT_BY_ISCYCLE 偽列
該偽列指明該行的子結點是否也是該行的父結點。必須使用 NOCYCLE 關鍵字

select empid,name,mgrid,level,CONNECT_BY_ISCYCLE
from employee
start with name = 'Goyal' connect by NOCYCLE prior empid=mgrid;
例：
UPDATE employee SET mgrid = 5 WHERE empid = 17;
SELECT empid, name, mgrid,
CONNECT_BY_ISLEAF leaf, CONNECT_BY_ISCYCLE cycle
FROM employee
START WITH name = 'Goyal'
CONNECT BY NOCYCLE PRIOR empid = mgrid;

六、 CONNECT_BY_ISLEAF 偽列
該偽列標識該行在此次查詢中是否還有子結點。

select empid,name,mgrid,level,CONNECT_BY_ISLEAF is_leaf
from employee
start with name = 'Goyal' connect by prior empid=mgrid;

七、 SYS_CONNECT_BY_PATH 函式
返回一個字串：表示根節點到當前節點的路徑

select empid,name,mgrid,level,trim(SYS_CONNECT_BY_PATH(name,'/'))
from employee
start with name = 'Goyal' connect by prior empid=mgrid;

八、 SIBLINGS keyword in the ORDER BY clause
相當於深度優先遍歷.

select empid,name,mgrid,level
from employee
start with name = 'Goyal' connect by prior empid=mgrid
ORDER SIBLINGS BY name asc;
(各兄弟之間按名字升序排列)