樂在其中

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);

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                               

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