insert into test_rec values (1,NULL, 'Россия');
insert into test_rec values (2,1, 'Москва');
insert into test_rec values (3,2, 'Головной');
insert into test_rec values (4,1, 'Приморье');
insert into test_rec values (5,4, 'Офис 1');
insert into test_rec values (6,4, 'Офис 2');
Делаем рекурсивный обход:
WITH RECURSIVE Rec AS (
SELECT id, descr, 0 as levels FROM test_rec where pid is null
UNION all
SELECT test_rec.id, test_rec.descr, levels + 1
FROM Rec inner join test_rec on Rec.id = test_rec.pid /*отношение*/
)
SELECT * FROM Rec;
Понимание запроса:
WITH RECURSIVE говорит о том, что работаем с рекурсией.
Rec AS - создаем временную таблицу.
SELECT id, title, 0 as levels FROM test_table where pid is null показывает, с какой записи начинается чтение таблицы (запись с пустым предком).
UNION all SELECT test_table.id, test_table.title, levels + 1 FROM Rec inner join test_table on Rec.id = test_table.pid - сам рекурсивный обход. Обращаем внимание на используемые имена таблиц в запросе. К переменной levels добавляем 1. inner join задает соотношение один к одному.
SELECT * FROM Rec - вывод результата.
С иерархическими списками я столкнулся при работе со справочниками в 1с. Пример иерархического списка - справочник ТМЦ и т.д. Думаю, что это - не первая и не последняя система с иерархической структурой справочников.
Ссылки на материалы:
Пример основан на: http://habrahabr.ru/post/43955/, является полной переработкой и заточен под мои цели. Очень жаль, что у автора нет времени писать работающие запросы.