Исходный код вики Рекурсивный запрос
Версия 1.1 от Alexandr Fokin на 2020/07/18 14:02
Последние авторы
| author | version | line-number | content |
|---|---|---|---|
| 1 | {{code language="sql"}} | ||
| 2 | |||
| 3 | with recursive tree (nm, id, level, pathstr) | ||
| 4 | as | ||
| 5 | ( | ||
| 6 | --Первый элемент в выборке. Начало рекурсии | ||
| 7 | select | ||
| 8 | nm, id, 0, cast('' as text) | ||
| 9 | from tree_sample | ||
| 10 | where id_parent is null | ||
| 11 | |||
| 12 | union all | ||
| 13 | |||
| 14 | --Каждый последующий элемент рекурсии | ||
| 15 | select | ||
| 16 | tree_sample.nm, tree_sample.id, t.level + 1, tree.pathstr + tree_sample.nm | ||
| 17 | from tree_sample | ||
| 18 | inner join tree | ||
| 19 | on tree.id = tree_sample.id_parent | ||
| 20 | ) | ||
| 21 | |||
| 22 | select | ||
| 23 | id, space( level ) + nm as nm | ||
| 24 | from tree | ||
| 25 | order by pathstr | ||
| 26 | |||
| 27 | {{/code}} |