Вопрос:

С оговоркой: гнездящиеся деревья

sql sqlite common-table-expression

60 просмотра

1 ответ

731 Репутация автора

У меня есть древовидная иерархия с порядковым порядком родного брата. Мне нужно добавить ссылку на другие деревья.

Вот данные:

drop table if exists org; CREATE TABLE org(id int primary key, name text, boss int, sibling int, ref int) without rowid;
INSERT INTO org VALUES(0, 'Alice', NULL, null, null);
INSERT INTO org VALUES(1, 'Bob', 0, null, null);
INSERT INTO org VALUES(2, 'Cindy', 0, 1, null);
INSERT INTO org VALUES(3, 'Dave', 1, 4, 7);
INSERT INTO org VALUES(4, 'Emma', 1, null, null);
INSERT INTO org VALUES(5, 'Fred', 2, null, null);
INSERT INTO org VALUES(6, 'Gail', 2, 5, null);
INSERT INTO org VALUES(7, 'Helen', NULL, null, null);
INSERT INTO org VALUES(8, 'Igor', 7, null, null);
INSERT INTO org VALUES(9, 'Jerome', 7, 8, null);

Дэйв ссылается на дерево во главе с Хелен.

Я добавил пункт refs:

WITH RECURSIVE
refs(id, name, boss, sibling, ref, lref) AS (
 SELECT id, name, boss, sibling, ref, 0 FROM org
UNION ALL
 SELECT org.id, org.name, org.boss, org.sibling, org.ref, refs.lref+1
 FROM org JOIN refs ON org.id=refs.ref
),
sibs(id, name, boss, lref, lsib) AS (
 SELECT id, name, boss, lref, 0 FROM refs
 WHERE sibling IS NULL
UNION ALL
 SELECT refs.id, refs.name, refs.boss, refs.lref, sibs.lsib + 1
 FROM refs
 JOIN sibs ON refs.boss = sibs.boss
 AND refs.sibling = sibs.id
),
tree(id, name, lsib, lref, level) AS (
 select id, name, 0, 0, 0 from org where id = 0
UNION ALL
 SELECT sibs.id, sibs.name, sibs.lsib, sibs.lref, tree.level+1
 FROM sibs JOIN tree ON sibs.boss=tree.id
ORDER BY 4 DESC, 5 DESC, 3 DESC
)
SELECT group_concat(name) FROM tree;

Но результат не включает дерево Хелен:

'Alice,Cindy,Gail,Fred,Bob,Dave,Emma'

Как я могу получить полный результат с деревом Елены:

'Alice,Cindy,Gail,Fred,Bob,Dave,Helen,Igor,Jerome,Emma'
Автор: Simon Источник Размещён: 06.04.2019 02:09

Ответы (1)


0 плюса

23281 Репутация автора

Прежде всего, я думаю, что есть проблема в ожидаемом результате:

Алиса, Синди, Гейл, Фред, Боб, Дэйв, Хелен, Игорь, Иероним, Эмма

Так как это должно быть

Алиса, Синди, Гейл, Фред, Боб, Дэйв, Эмма, Хелен Джером, Игорь

Потому что Эмма под Бобом, а Джером и Игорь под Хелен

|--Alice
    |--Cindy
        |----Gail
        |----Fred
    |--Bob
        |----Dave
        |----Emma
|--Helen
    |--Jerome
    |-- Igor

Решение

В Treeтабличном выражении вы должны заменить:

select id, name, 0, 0, 0 from org where id = 0

С

select id, name, 0, 0, 0 from org where boss IS NULL

запрос

WITH RECURSIVE
refs(id, name, boss, sibling, ref, lref) AS (
 SELECT id, name, boss, sibling, ref, 0 FROM org
UNION ALL
 SELECT org.id, org.name, org.boss, org.sibling, org.ref, refs.lref+1
 FROM org JOIN refs ON org.id=refs.ref
),
sibs(id, name, boss, lref, lsib) AS (
 SELECT id, name, boss, lref, 0 FROM refs
 WHERE sibling IS NULL
UNION ALL
 SELECT refs.id, refs.name, refs.boss, refs.lref, sibs.lsib + 1
 FROM refs
 JOIN sibs ON refs.boss = sibs.boss
 AND refs.sibling = sibs.id
),
tree(id, name, lsib, lref, level) AS (
 select id, name, 0, 0, 0 from org where boss IS NULL
UNION ALL
 SELECT sibs.id, sibs.name, sibs.lsib, sibs.lref, tree.level+1
 FROM sibs JOIN tree ON sibs.boss=tree.id
ORDER BY 4 DESC, 5 DESC, 3 DESC
)
SELECT group_concat(name) FROM tree;

Результат

Алиса, Синди, Гейл, Фред, Боб, Дэйв, Эмма, Хелен Джером, Игорь

Автор: Hadi Размещён: 08.04.2019 11:20
Вопросы из категории :
32x32