Oracle SQL: представления семейного дерева с использованием деревьев

Question

Я хочу создать представление, которое дает мне внуков самого старого человека, у которого есть некоторые. И проблема в том, что я не могу найти способ перевести в SQL фразу: "у кого есть".

Я работаю только в одной таблице, которая довольно проста:

Персона: номер (номер типа), фамилия, имя, дата рождения, пол, мать (номер типа), отец (номер типа).

Вот что я пытался сделать:

Я пытался создать представление, которое дает мне самый старый человек, у которого есть внуки (но это не то, что здесь делается)

  CREATE OR REPLACE
  VIEW oldestone
  AS SELECT number FROM persons
     WHERE (sysdate-dateofbirth)/365 >= ALL
     (SELECT (sysdate-dateofbirth)/365 FROM persons)
     AND EXISTS (SELECT * FROM persons
                  WHERE level=3 
            START WITH number = number
            CONNECT BY PRIOR number = father OR PRIOR numero = mother);

И с номером первого просмотра я могу получить внуков:

CREATE OR REPLACE
VIEW grandchildren
AS SELECT firstname,lastname FROM persons
   WHERE level=3 
   START WITH number = (SELECT number FROM oldestone)
   CONNECT BY PRIOR number = father OR PRIOR number = mother;

Проблема в том, что я знаю, что не переводю: внуки самого старого человека, у которого есть. Потому что, на мой первый взгляд, когда я написал число = число, я хочу сослаться на номер строки 3 моего предложения выбора, но я знаю, что это не так.

Заранее спасибо за помощь, ребята!

Крис.

Answer 1

Если вы измените направление чтения "вверх", а не "вниз", вы можете перейти от внуков к их родителям, а затем к бабушке и дедушке. При этом в самом внутреннем подзапросе вы можете "запомнить" имена внуков с помощью connect_by_root() оператор и связать их с датами рождения их предков на разных уровнях (их собственные документы на уровне 1, документы их родителей на уровне 2, документы их бабушек и дедушек на уровне 3).

В промежуточном подзапросе я выбираю строки, созданные иерархическим запросом на уровне 3, которые будут показывать даты рождения бабушек и дедушек. Я использую аналитическую функцию для записи минимальной даты рождения в том же запросе. (Вам не нужен "возраст" - "самый старый" означает самую раннюю дату рождения!)

Я написал запрос настолько экономно, насколько это возможно, на каждом этапе оставляя только столбцы, необходимые для получения требуемого результата (имена внуков самых старых бабушек и дедушек); когда вы выбираете подзапрос и промежуточный подзапрос и запускаете их отдельно, чтобы понять, как это работает, вы можете добавить дополнительные столбцы, чтобы просто посмотреть, что происходит.

Я создал несколько очень простых тестовых данных в предложении WITH (не является частью решения); Вы можете проверить с более интересными входами. Удачи!

with
     person ( id, last_name, first_name, dob, mother, father ) as (
       select  1, 'Doe', 'John', date '1990-03-02',  2,  3 from dual union all
       select  2, 'Doe', 'Anne', date '1962-11-21',  4,  5 from dual union all
       select  3, 'Doe', 'Alan', date '1960-02-23',  6,  7 from dual union all
       select  4, 'Orf', 'Jean', date '1953-10-11',  8,  9 from dual union all
       select  5, 'Orf', 'Stan', date '1952-09-06', 10, 11 from dual union all
       select 22, 'Sun', 'Ryan', date '1968-02-21', 23, 24 from dual union all
       select 23, 'Sun', 'Mary', date '1934-12-09', 26, 27 from dual
     )
--  end of test data; solution (SQL query) begins below this line
select last_name, first_name
from   (
         select last_name, first_name, dob, min(dob) over () as min_dob
         from   (
                  select connect_by_root(last_name)  as last_name ,
                         connect_by_root(first_name) as first_name, dob, level as lvl
                  from   person
                  connect by id in (prior mother, prior father)
         ) 
         where  lvl = 3
)
where  dob = min_dob
;

LAST_NAME  FIRST_NAME
---------  ----------
Doe        John

Answer 2

Стол:

создать таблицу FAMILYTREE (id int, name varchar(50), MOTHERID int, FATHERID int);/

вставить в FAMILYTREE(id, MOTHERID, FATHERID, name) значения (1, null, null, 'My Grand Father'); вставить в FAMILYTREE(id, MOTHERID, FATHERID, name) значения (2, NULL, NULL, 'My Grand Mother'); вставить в СЕМЕЙНОЕ ДЕРЕВО (ID, MotherID, FatherID, Name) VALUES (3, 10, 9, «Моя мама»); вставить в СЕМЕЙНОЕ ДЕРЕВО (ID, MotherID, FatherID, Name) VALUES (4, 2, 1, «Мой отец»); вставить в СЕМЕЙНОЕ ДЕРЕВО (ID, MotherID, FatherID, Name) VALUES (5, 3, 4, 'Me'); вставить в СЕМЕЙНОЕ ДЕРЕВО (ID, MotherID, FatherID, Name) VALUES (6, 12, 11, 'Моя жена'); вставить в СЕМЕЙНОЕ ДЕРЕВО (ID, MotherID, FatherID, Name) VALUES (7, 3, 4, «Мой брат»); вставить в СЕМЕЙНОЕ ДЕРЕВО (ID, MotherID, FatherID, Name) VALUES (8, 6, 5, «Мой сын»); вставить в FAMILYTREE (ID, MotherID, FatherID, Name) VALUES (9, NULL, NULL, 'Моя мать, дедушка »); вставить в FAMILYTREE (ID, MotherID, FatherID, Name) VALUES (10, NULL, NULL, 'Моя мать бабушка'); вставить в FAMILYTREE (ID, MotherID, FatherID, Name) VALUES (11, null, null, «Моя жена, дедушка»); вставить в FAMILYTREE(id, MOTHERID, FATHERID, name) значения (12, null, null, 'My Wife Grand Mother'); совершить;/

SQl:

WITH FamilyCTE (ID, имя, MotherID,FATHERID, FatherName, MOTHERNAME,LVL) как (выберите f.*, Null как FATHERNAME,
null как MOTHERNAME, 0 как LVL из FAMILYTREE F, где F.FATHERID равно null и f.MotherID IS NULLUNION ALLSELECTf.ID,f.Name AS ParentName,f.MotherID,F.FATHERID,c.Name AS FatherName,C2.name как MOTHERNAME,lvl + 1 из FAMILYTREE F INNER JOIN FamilyCTE c ON F.FatherID = c. ID ВНУТРЕННЕЕ СОЕДИНЕНИЕ FamilyTree c2 НА f.MotherID = c2.ID)

выберите * из FAMILYCTE;