DB2 递归查询

lihu129c

　　上一篇中讲解了ORACLE中的递归查询，下面我们看一下DB2中如何使用递归查询：
　　同样的我们先新建一个表来存储以上信息，并插入测试数据：



--建表
create table FAMILY
(
person_id INTEGER,
name      VARCHAR(10),
parent_id INTEGER
);
--插入测试数据
insert into FAMILY (PERSON_ID, NAME, PARENT_ID) values (1, 'Apple', 2);
insert into FAMILY (PERSON_ID, NAME, PARENT_ID) values (2, 'Zoe', 4);
insert into FAMILY (PERSON_ID, NAME, PARENT_ID) values (3, 'Tom', 4);
insert into FAMILY (PERSON_ID, NAME, PARENT_ID) values (4, 'John', 7);
insert into FAMILY (PERSON_ID, NAME, PARENT_ID) values (5, 'Peter', 6);
insert into FAMILY (PERSON_ID, NAME, PARENT_ID) values (6, 'Robert', 10);
insert into FAMILY (PERSON_ID, NAME, PARENT_ID) values (7, 'Mary', null);
insert into FAMILY (PERSON_ID, NAME, PARENT_ID) values (8, 'Jenny', 7);
insert into FAMILY (PERSON_ID, NAME, PARENT_ID) values (9, 'Sam', 10);
insert into FAMILY (PERSON_ID, NAME, PARENT_ID) values (10, 'Li Lei', null);
　　在DB2中使用with字句实现递归查询：



WITH temp (person_id, name, parent_id, LEVEL)
AS (SELECT person_id,
name,
parent_id,
0
FROM FAMILY
WHERE person_id = '1' --找寻person_id=1的人的祖先
UNION ALL
SELECT SUPER.person_id,
SUPER.name,
SUPER.parent_id,
CHILD.LEVEL + 1
FROM FAMILY SUPER, temp CHILD
WHERE SUPER.person_id = CHILD.parent_id)
SELECT * FROM temp
　　得到 ，如图我们可以得到Apple的父亲是Zoe,Zoe父亲是John，看level依次类推，Mary是他们老祖宗，
　　当然，我们除了可以得到某个节点的父节点，



WITH temp (person_id, name, parent_id, LEVEL)
AS (SELECT person_id,
name,
parent_id,
1
FROM FAMILY
WHERE person_id = '4' --找寻person_id=4的人的祖先
UNION ALL
SELECT SUPER.person_id,
SUPER.name,
SUPER.parent_id,
CHILD.LEVEL + 1
FROM FAMILY SUPER, temp CHILD
WHERE SUPER.person_id = CHILD.parent_id)
SELECT * FROM temp
　　也可以得到某个节点的子节点：



WITH temp (person_id, name, parent_id, LEVEL)
AS (SELECT person_id,
name,
parent_id,
1
FROM FAMILY
WHERE person_id = '4' --找寻person_id=4的人的传人
UNION ALL
SELECT CHILD.person_id,
CHILD.name,
CHILD.parent_id,
SUPER.LEVEL + 1
FROM FAMILY CHILD, temp SUPER
WHERE SUPER.person_id = CHILD.parent_id)
SELECT * FROM temp
　　得到 ，其中Tom跟Zoe都是John的子节点。