Для всех запросов в статье используется синтаксис СУБД SQLite. Для других СУБД синтаксис может незначительно отличаться.
Чтобы представить древовидную структуру в реляционной базе данных, можно создать следующую таблицу:
create table tree (
id integer,
parent integer,
name text,
primary key (id),
foreign key (parent) references tree(id)
on update cascade on delete cascade
);
В поле parent
задается родитель для элемента древовидной структуры, причем в качестве значения этого поля используется значение поля id
другого элемента. В поле name
задается имя элемента.
Для наглядности зададимся древовидной структурой:
- Овощи
- Картофель
- Томаты
- Томат обыкновенный
- Томат Перуанский
- Фрукты
Такую структуру можно создать следующими запросами:
insert into tree values ( 1, null, 'Овощи');
insert into tree values ( 2, 1, 'Картофель');
insert into tree values ( 3, 2, 'Аспиа');
insert into tree values ( 4, 2, 'Виталот');
insert into tree values ( 5, 2, 'Диво');
insert into tree values ( 6, 1, 'Томаты');
insert into tree values ( 7, 6, 'Томат обыкновенный');
insert into tree values ( 8, 6, 'Томат перуанский');
insert into tree values ( 9, null, 'Фрукты');
insert into tree values (10, 9, 'Груши');
insert into tree values (11, 10, 'Груша обыкновенная');
insert into tree values (12, 9, 'Яблоко');
Здесь null
означает корневую группу.
Предположим, что требуется извлечь данные из таблицы tree
в следующем виде:
/Овощи
/Фрукты
/Овощи/Картофель
/Овощи/Томаты
/Фрукты/Груши
/Фрукты/Яблоко
/Овощи/Картофель/Аспиа
/Овощи/Картофель/Виталот
/Овощи/Картофель/Диво
/Овощи/Томаты/Томат обыкновенный
/Овощи/Томаты/Томат перуанский
/Фрукты/Груши/Груша обыкновенная
Для этого понадобится рекурсивный запрос:
with cte as (
select
id,
parent,
'/' || name as name
from tree where parent is null
union all
select
tree.id,
tree.parent,
cte.name || '/' || tree.name
from cte, tree on tree.parent = cte.id
)
select name from cte;
Рекурсивные запросы поддерживаются в SQLite начиная с версии 3.8.3 от 3 февраля 2014 года.
Понравилось это:
Нравится Загрузка...