DB2 递归查询

上一篇中讲解了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的子节点。