如何获得亲属关系

Question

我有这些桌子

具有下列数据的person表

person_id    description

1          first in the family
2          SON OF PERSON 1
3          SON OF PEROSN 2
4          SON OF PEROSN 3
5          SON OF PERSON 4
6          SON OF  PERSON 2
7          SON OF  PERSON 3
8          SON OF PERSON 3
9          SON OF PERSON 7

具有以下数据的person_name表

person_id  first_name

1        Santiago
2         Lautaro
3          Lucas
4          Franco
5          Agustín
6          MOLE
7          BULLY
8          CIVAL
9          NUMA

具有以下数据的父表

person_father_id  description
1              father of Lautaro
2              father of Lucas
3              father of Franco
4              father of Agustín
7              FATHER OF NUMA

具有下列数据的子表

 person_child_id   person_father_id
  2                 1
  3                 2
  4                 3
  5                 4
  6                 2
  7                 3
  8                 3
  9                 7

如何处理家庭关系，例如4号人的叔叔，2号人的侄子和4号人的父亲。

Answer 1

SQL Fiddle

Oracle 11g R2架构设置

CREATE TYPE number_table AS TABLE OF NUMBER;

CREATE TABLE relationships (child_id, father_id ) AS
SELECT 2, 1 FROM DUAL UNION ALL
SELECT 3, 2 FROM DUAL UNION ALL
SELECT 4, 3 FROM DUAL UNION ALL
SELECT 5, 4 FROM DUAL UNION ALL
SELECT 6, 2 FROM DUAL UNION ALL
SELECT 7, 3 FROM DUAL UNION ALL
SELECT 8, 3 FROM DUAL UNION ALL
SELECT 9, 7 FROM DUAL;

查询1

SELECT n.COLUMN_VALUE ||' IS THE NEPHEW OF ' || u.COLUMN_VALUE AS nephew
FROM   (
         SELECT children AS nephews,
                PRIOR CHILDREN MULTISET EXCEPT NUMBER_TABLE( father_id ) AS uncles
         FROM   (
           SELECT father_id,
                  CAST( COLLECT( child_id ORDER BY child_id ) AS number_table ) AS children
           FROM   relationships
           GROUP BY father_id
         )
         WHERE LEVEL = 2
         CONNECT BY father_id MEMBER OF PRIOR children
       ) t
       CROSS JOIN TABLE( t.nephews ) n
       CROSS JOIN TABLE( t.uncles ) u

结果

|               NEPHEW |
|----------------------|
| 4 IS THE NEPHEW OF 6 |
| 7 IS THE NEPHEW OF 6 |
| 8 IS THE NEPHEW OF 6 |
| 5 IS THE NEPHEW OF 7 |
| 5 IS THE NEPHEW OF 8 |
| 9 IS THE NEPHEW OF 4 |
| 9 IS THE NEPHEW OF 8 |

查询2

SELECT PRIOR father_id || ' IS THE GRANDFATHER OF ' || child_id AS grandfather
FROM   relationships
WHERE  LEVEL = 2
CONNECT BY father_id = PRIOR child_id

结果

|               GRANDFATHER |
|---------------------------|
| 1 IS THE GRANDFATHER OF 3 |
| 1 IS THE GRANDFATHER OF 6 |
| 2 IS THE GRANDFATHER OF 4 |
| 2 IS THE GRANDFATHER OF 8 |
| 2 IS THE GRANDFATHER OF 7 |
| 3 IS THE GRANDFATHER OF 5 |
| 3 IS THE GRANDFATHER OF 9 |