Блог

у меня есть таблица в hsqldb и значения, подобные этому

Как мне написать запрос, который отображается следующим образом

Поехали:

 with recursive
n (root_id, id, title, parent) as (
  select id as root_id, id, name, parent from t
 union
  select n.root_id, t.id, t.name || '/' || n.title, t.parent
  from n
  join t on t.id = n.parent
)
select title as name from n where parent is null order by root_id
  

Результат:

 NAME      
----------
A         
A/A1      
B         
B/B1      
C         
C/C1      
A/A1/A2   
A/A1/A3   
A/A1/A3/A4
B/B1/B3   
  

Для справки это скрипт данных, который я использовал для тестирования:

 create table t (id int, name varchar(10), parent int);

insert into t (id, name, parent) values
  (0, 'A', null),
  (1, 'A1', 0),
  (2, 'B', null),
  (3, 'B1', 2),
  (4, 'C', null),
  (5, 'C1', 4),
  (6, 'A2', 1),
  (7, 'A3', 1),
  (8, 'A4', 7),
  (9, 'B3', 3);
  

Вы можете использовать рекурсивный запрос:

 with recursive cte (id, name, parent, path, lvl) as
    select id, name, parent, name, 0 from mytable
    union all
    select c.id, c.name, t.parent, concat(t.name, '/', c.path), c.lvl   1
    from cte c
    inner join mytable t on t.id = c.parent_id
)
select *
from cte c
where lvl = (select max(c1.lvl) from cte c1 where c1.id = c.id)
  

Запрос выбирает всю таблицу, затем поднимается по иерархическому дереву каждой строки. Преимуществом этого подхода является то, что он будет правильно обрабатывать «сломанное» дерево (например, дочернее с недопустимым родительским элементом).

Альтернативный, более простой запрос, основанный на принятом ответе. Мы начинаем только с корней (строк с нулевыми родительскими элементами), и каждый раз, когда выполняется объединение, добавляется только несколько строк. Как следует из ответа, этот тип запроса не будет выбирать дочерние строки с недопустимым родительским идентификатором:

 with recursive n (root_id, id, title, parent) as (
  select id as root_id, id, name, parent from t where parent is null
 union
  select n.root_id, t.id, n.title || '/' || t.name, t.parent
  from t
  join n on n.id = t.parent
)
select * from n
  

Для таблиц такого типа важно добавить ссылочные ограничения, которые неявны в дизайне. Это ускорит запросы, когда таблица станет больше, а также позволит избежать поврежденных родительских идентификаторов.

 alter table t add constraint pk primary key(id)
alter table t add constraint fk foreign key (parent) references t(id)