DB2的递归查询

CREATE

    TABLE FAMILY

    (

        PERSON_ID INTEGER,

        NAME VARCHAR(50),

        AGE INTEGER,

        GENDER CHARACTER(1),

        PARENT_ID INTEGER

    )

 

insert into FAMILY (PERSON_ID, NAME, AGE, GENDER, PARENT_ID) values 

(1, 'Apple', 10, 'F', 10),

(2, 'Zoe', 11, 'F', 3),

(3, 'John', 30, 'M', 13),

(4, 'Mary', 25, 'F', 24),

(5, 'Peter', 14, 'M', 4),

(6, 'Jenny', 13, 'F', 4),

(24, 'Robert', 60, 'M', 30);

查询Jenny的祖先

WITH temptab (person_id, name, parent_id, LEVEL) AS          

     (SELECT person_id, name, parent_id, 0               

        FROM FAMILY

       WHERE name = 'Jenny'

      UNION ALL                                      

      SELECT SUPER.person_id, SUPER.name, SUPER.parent_id, CHILD.LEVEL+1         

        FROM FAMILY SUPER, temptab CHILD

       WHERE SUPER.person_id = CHILD.parent_id

) SELECT * FROM temptab                               

查询Robert的子孙

WITH temptab (person_id, name, parent_id, level) AS          

     (SELECT person_id, name, parent_id, 0               

        FROM FAMILY

       WHERE name = 'Robert'

      UNION ALL                                      

      SELECT CHILD.person_id, CHILD.name, CHILD.parent_id, SUPER.LEVEL+1         

        FROM FAMILY CHILD, temptab SUPER

       WHERE SUPER.person_id = CHILD.parent_id AND level < 2

) SELECT * FROM temptab WHERE LEVEL<>0