СУБД ЛИНТЕР — Документация

Иерархический запрос

Функция

Определение иерархического запроса к таблице.

Спецификация

<иерархический запрос>::=

[START WITH начальный уровень]
[CONNECT BY условие связи]
[ORDER SIBLINGS BY имя столбца]

<начальный уровень>::=

предикат [, …]

<условие связи>::=

предыдущий уровень = последующий уровень
[предикат условие связи, ...]

<предыдущий уровень>::=

PRIOR значимое выражение

<последующий уровень>::=

значимое выражение

Синтаксические правила

Опция START WITH задает условия выбора начальной записи иерархической структуры данных.
Опция CONNECT BY <условие связи> задает отношение между узлами иерархической структуры данных. Часть элементов <условия связи> должна быть использована в опции PRIOR для ссылки на предыдущий уровень иерархии.
Опция PRIOR устанавливает предыдущий уровень текущей записи иерархии. PRIOR – унарный оператор и обрабатывается как обычные унарные + и - операторы. В неиерархических запросах при одной исходной таблице во всех <условиях связи> имена столбцов (не считая внешних ссылок) рассматриваются как ссылки к одной и той же записи исходной таблицы. Напротив, в иерархическом запросе <условие связи> должно содержать ссылки к двум записям – текущего и предыдущего уровня, которые оно и связывает между собой.
В <условиях связи> <предыдущий уровень> и <последующий уровень> можно менять местами. <Условие связи> должно содержать ссылки к двум записям – текущего и предыдущего уровня, которые связывает между собой. Но так как в иерархическом запросе может участвовать только одна таблица, то различие между обращениями к записям текущего и предыдущего уровня делается следующим образом: если перед аргументом предиката стоит ключевое слово PRIOR, то все обращения к столбцам в этом аргументе предиката относятся к записи предыдущего уровня, если же PRIOR не стоит – то к записи текущего уровня.

Эти конструкции эквивалентны:
```
select *
FROM "Служащие"
START WITH "Должность" = 'РУКОВОДИТЕЛЬ'
CONNECT BY PRIOR "Таб_Номер"="Таб_Номер_Руковод";
```
```
select *
FROM "Служащие"
START WITH "Должность" = 'РУКОВОДИТЕЛЬ'
CONNECT BY "Таб_Номер_Руковод" = PRIOR "Таб_Номер";
```
<Предыдущий уровень> и <последующий уровень> должны ссылаться на <значимые выражения>, содержащие разные столбцы таблицы.
Типы данных <предыдущего уровня> и <последующего уровня> должны быть совместимыми.
Условия WHERE, START и CONNECT в иерархических запросах можно задавать в произвольном порядке.
Столбец LEVEL внутри иерархического запроса используется для обозначения уровня иерархии, в остальных запросах – как идентификатор (имя столбца).
```
create table tst(s int, f int);
insert into tst values (1,2), (2,3);

SELECT level, COUNT(*)
FROM (SELECT level FROM TST START WITH s=1 CONNECT BY S=PRIOR f) a
GROUP BY level;
или
with t1 as (SELECT level FROM TST START WITH s=1 CONNECT BY S=PRIOR f)
SELECT level, COUNT(*)
FROM t1 GROUP BY level;


LEVEL
 -----
|          1|          1|
|          2|          1|
```
Примечание

Столбец LEVEL в опции CONNECT BY обрабатывается корректно только для простых условий типа LEVEL <значение> (для сложных условий результат может быть не верным).

Опция SIBLINGS в <ORDER BY-спецификации> задаёт порядок сортировки записей внутри уровней иерархического запроса. (Без сортировки вывод записей выполняется в порядке обхода иерархического дерева, т.е. сверху-вниз, слева-направо).

create or replace table test(name char(5), id int, parent int);
insert into test values ('F', 1, NULL);
insert into test values ('C', 2, 1);
insert into test values ('H', 3, 2);
insert into test values ('E', 4, 2);
insert into test values ('B', 5, 1);
insert into test values ('G', 6, 5);
insert into test values ('D', 7, 6);
insert into test values ('A', 8, 5);

а) обычная сортировка

select name, id , parent , level from test start with parent is null
connect by prior id = parent order by name;
NAME  ID          PARENT      LEVEL
----  --          ------      -----
|A    |          8|          5|          3|
|B    |          5|          1|          2|
|C    |          2|          1|          2|
|D    |          7|          6|          4|
|E    |          4|          2|          3|
|F    |          1|           |          1|
|G    |          6|          5|          3|
|H    |          3|          2|          3|

б) иерархическая сортировка

select name, id , parent , level from test start with parent is null
connect by prior id = parent order siblings by name;
NAME  ID          PARENT      LEVEL
 ----  --          ------      -----
|F    |          1|           |          1|
|B    |          5|          1|          2|
|A    |          8|          5|          3|
|G    |          6|          5|          3|
|D    |          7|          6|          4|
|C    |          2|          1|          2|
|E    |          4|          2|          3|
|H    |          3|          2|          3|

Запрещается использовать одновременно <WHERE-спецификацию> и <ORDER BY-спецификацию> с опцией SIBLINGS. Т.е. запрос вида
```
select * from test start with parent is null
connect by prior id = parent where id <> 1 order siblings by name;
```
является синтаксически неправильным.
Псевдостолбцы ROWID и ROWTIME в иерархических запросах запрещены. Например, нижеследующие запросы синтаксически некорректны:
```
select * from TEST start with PARENT is null connect
by prior ID = PARENT ORDER by ROWID;

select * from TEST start with PARENT is null connect
by prior ID = PARENT ORDER siblings by ROWID;
```

Опция GROUP BY в иерархических запросах разрешена, например, нижеследующий запрос:

select COUNT(*) COUNT,LEVEL from test start with PARENT is null connect
by prior ID = PARENT GROUP BY LEVEL;
COUNT LEVEL
 ----------
|   1|    1|
|   2|    2|
|   4|    3|
|   1|    4|

Общие правила

Результирующая таблица по умолчанию имеет тот же список столбцов, что и исходная, с добавлением псевдостолбца с именем «LEVEL» типа INTEGER, который указывает номер уровня записи в иерархии.
Наличие псевдостолбца с именем «LEVEL» в выборке вида SELECT * FROM… зависит от ключа /COMPATIBILITY=ORACLE в команде запуска ядра СУБД:
- псевдостолбец будет присутствовать, если ключ не задан;
- псевдостолбец будет отсутствовать, если ключ задан.
Псевдостолбец LEVEL можно указывать в <основном запросе выборки> и <WHERE-спецификации> и нельзя в конструкциях START WITH и CONNECT BY.
Сортировка записей в ответе (если не задана <GROUP BY-спецификация>) выполняется в таком порядке, чтобы все записи, подчиненные в иерархии некоторой записи, следовали за ней.
Условие, содержащееся в конструкции START, определяет записи верхнего уровня в иерархии.
Условие, содержащееся в конструкции CONNECT, определяет связи между записями предыдущего и последующего уровней.
<Иерархический запрос> выполняется столько раз, сколько <начальных уровней> найдено в таблице, задаваемой <FROM-спецификацией>.
Максимальное количество уровней для сортируемых иерархических запросов равно 4096.
Если в иерархии записей обнаружено нарушение структуры дерева, фиксируется исключительная ситуация.
Для запросов с CONNECT BY можно подавать команды позиционного обновления и удаления (WHERE CURRENT OF).

Примеры

create or replace table tab1 (id int, "Фамилия" char(15), "Имя" char(10), "Год рождения" int, "Статус" char(10),
"Поколение" int, "Отношение" int);
insert into tab1 values(10,'Петрова', 'Мария', 1989, 'дочь', 3,5);
insert into tab1 values(9,'Петров', 'Сергей', 1989, 'сын', 3, 5) ;
insert into tab1 values(8,'Иванов', 'Сергей', 1989, 'сын', 3,3 );
insert into tab1 values(7,'Иванов', 'Михаил', 1994, 'сын', 3,4 );
insert into tab1 values(6,'Иванов', 'Иван', 1972, 'сын', 2, 1);
insert into tab1 values(5,'Петрова', 'Анна', 1970, 'дочь', 2, 1);
insert into tab1 values(4,'Иванов', 'Андрей', 1974, 'сын', 2, 1);
insert into tab1 values(3,'Иванов', 'Сергей', 1968, 'сын', 2,1 );
insert into tab1 values(2,'Иванова', 'Мария', 1950, 'бабушка', 1,0);
insert into tab1 values(1,'Иванов', 'Иван', 1948, 'дедушка', 1,0);

Сколько детей и внуков у Иванова Ивана:

select count(*)-1 from tab1 start with id=1
connect by prior id="Отношение" and "Отношение"<>0 ;
|8 |

Сколько всего внучат:

select count(*) from tab1 start with "Поколение"=3
  connect by prior id="Отношение" and
  prior id="Поколение" and "Отношение"<>0 ;
|4 |

Сколько всего внучат, родившихся до 1990 года:

select count(*) from tab1 start with "Поколение"=3
  connect by prior id="Отношение" and
prior id="Поколение" and "Отношение"<>0 where "Год рождения"<1990;
|3 |

Сколько внучат от 10 до 15 лет:

select count(*) from tab1 start with "Поколение"=3 and datesplit(to_date('2000','yyyy'),'y')- "Год рождения" between 10 and 15
  connect by prior id="Отношение" and prior id="Поколение" and "Отношение"<>0 ;
|3 |

Сколько лиц мужского пола в семействе:

select count(id) from tab1 start with id=1
  connect by prior id="Отношение" and "Статус"='сын' and "Отношение"<>0 ;
|6 |

Сколько дядей и тетей у детей Анны Петровой (дочери Ивана Иванова):

select count(id)-1   from tab1
start with id=(select distinct id from tab1 where "Фамилия"='Иванов' and "Имя"='Иван' and "Поколение"=1)
  connect by prior id="Отношение"
  and "Фамилия" not like 'Петров%'
  and "Поколение"=2
  and "Отношение"<>0;
|3 |

Имена и фамилии дядей и тетей детей Анны Петровой (дочери Ивана Иванова):

select cast  "Фамилия" || ' '  || "Имя"  as char(30)  from tab1
where id in (select id from tab1
start with id=(select distinct id from tab1 where "Фамилия"= 'Иванов' and "Имя"= 'Иван' and "Поколение"=1)
  connect by prior id="Отношение"
  and "Фамилия" not like 'Петров%'
  and "Поколение"=2
  and "Отношение"<>0
except
select distinct id from tab1 where "Фамилия"= 'Иванов' and "Имя"= 'Иван' and "Поколение"=1);
|Иванов Сергей|
|Иванов Андрей|
|Иванов Иван  |

create table "Служащие"
(
  "Фамилия" char(40),
  "Таб_Номер" int primary key,
  "Таб_Номер_Руковод" int references "Служащие"("Таб_Номер"),
  "Должность" char(20)
);
INSERT INTO "Служащие" VALUES ('ИВАНОВ',7839,NULL,'РУКОВОДИТЕЛЬ');
INSERT INTO "Служащие" VALUES ('ПЕТРОВ',7698,7839,'МЕНЕДЖЕР');
INSERT INTO "Служащие" VALUES ('СИДОРОВ',7782,7839,'МЕНЕДЖЕР');
INSERT INTO "Служащие" VALUES ('СМИРНОВ',7566,7839,'МЕНЕДЖЕР');
INSERT INTO "Служащие" VALUES ('СИМОНОВ',7902,7566,'АНАЛИТИК');
INSERT INTO "Служащие" VALUES ('СОЛОВЬЕВ',7369,7902,'СЛУЖАЩИЙ');
INSERT INTO "Служащие" VALUES ('АНТОНОВ',7499,7698,'ПРОДАВЕЦ');
INSERT INTO "Служащие" VALUES ('СЕМЕНОВ',7521,7698,'ПРОДАВЕЦ');
INSERT INTO "Служащие" VALUES ('КУЗНЕЦОВ',7654,7698,'ПРОДАВЕЦ');
INSERT INTO "Служащие" VALUES ('МИРОНОВ',7788,7566,'АНАЛИТИК');
INSERT INTO "Служащие" VALUES ('ТИХОНОВ',7844,7698,'ПРОДАВЕЦ');
INSERT INTO "Служащие" VALUES ('БОРИСОВ',7876,7788,'СЛУЖАЩИЙ');
INSERT INTO "Служащие" VALUES ('ВАСИЛЬЕВ',7900,7698,'СЛУЖАЩИЙ');
INSERT INTO "Служащие" VALUES ('ТИХОНОВ',7934,7782,'СЛУЖАЩИЙ');
select *
FROM "Служащие"
START WITH "Должность" = 'РУКОВОДИТЕЛЬ'
CONNECT BY PRIOR "Таб_Номер" = "Таб_Номер_Руковод";

|Фамилия  |Таб_Номер|Таб_Номер_Рук|Должность    |LEVEL|
|ИВАНОВ   | 7839    | <NULL>      |РУКОВОДИТЕЛЬ | 1   |
|ПЕТРОВ   | 7698    | 7839        |МЕНЕДЖЕР     | 2   |
|АНТОНОВ  | 7499    | 7698        |ПРОДАВЕЦ     | 3   |
|СЕМЕНОВ  | 7521    | 7698        |ПРОДАВЕЦ     | 3   |
|КУЗНЕЦОВ | 7654    | 7698        |ПРОДАВЕЦ     | 3   |
|ТИХОНОВ  | 7844    | 7698        |ПРОДАВЕЦ     | 3   |
|ВАСИЛЬЕВ | 7900    | 7698        |СЛУЖАЩИЙ     | 3   |
|СИДОРОВ  | 7782    | 7839        |МЕНЕДЖЕР     | 2   |
|ТИХОНОВ  | 7934    | 7782        |СЛУЖАЩИЙ     | 3   |
|СМИРНОВ  | 7566    | 7839        |МЕНЕДЖЕР     | 2   |
|СИМОНОВ  | 7902    | 7566        |АНАЛИТИК     | 3   |
|СОЛОВЬЕВ | 7369    | 7902        |СЛУЖАЩИЙ     | 4   |
|МИРОНОВ  | 7788    | 7566        |АНАЛИТИК     | 3   |
|БОРИСОВ  | 7876    | 7788        |СЛУЖАЩИЙ     | 4   |

Если необходимо явно указать уровень иерархии в нужной позиции выборки, в запросе надо прописать имя LEVEL большими буквами в кавычках.

select "LEVEL","Фамилия" from "Служащие"
START WITH "Должность" = 'АНАЛИТИК' connect BY PRIOR "Таб_Номер"="Таб_Номер_Руковод";
LEVEL   Фамилия
|  1   | СИМОНОВ  |
|  2   | СОЛОВЬЕВ |
|  1   | МИРОНОВ  |
|  2   | БОРИСОВ  |