Рекурсивные СТЕ в SQL Продолжая тему иерархий, напомню, что имеются такие данные: ▶️Активы ➡️ Дебиторская задолженность ▶️Активы ➡️ Денежные средства ➡️ Касса ▶️Активы ➡️ Денежные средства ➡️ Расчётные счета Если представлять это в виде таблицы, то такая таблица имела бы поля: — account_id - id статьи — name - наименование статьи (активы, ден. ср-ва, касса и т.д.) — parent_id - id родительской статьи 🟠Сегодня речь пойдет о рекурсивных СТЕ как об альтернативе упомянутому ранее методу, потому что CONNECT BY - уникальная фича Oracle, в других стандартных диалектах такое не встречается. На примере наших данных для построения иерархии можно написать такой запрос: WITH RECURSIVE tree AS ( SELECT account_id, name, parent_id, 1 AS level FROM accounts WHERE parent_id IS NULL UNION ALL SELECT a.account_id, a.name, a.parent_id, t.level + 1 FROM accounts a JOIN tree t ON a.parent_id = t.account_id ) SELECT * FROM tree Такой запрос является самым простым, можно сказать, даже шаблонным, для построения типичной иерархии таким методом. 🟠Изначально СТЕ задается с помощью WITH RECURSIVE. Главное отличие от обычной СТЕ - это то, что рекурсивная может ссылаться сама на себя. Что это значит? В этом примере мы назвали СТЕ именем tree и начали его определять с помощью дальнейшего запроса. Но в JOIN к таблице accounts присоединяется tree, которое мы еще не закончили определять. Если бы запрос был составлен с помощью обычного СТЕ, то при таком JOIN была бы ошибка. 🟠Теперь касаемо логики построения иерархии. 1️⃣В первом SELECT выбираются те строки из accounts, где parent_id IS NULL, то есть выбираются начальные строки для нашей иерархии, прямо как в START WITH parent_id IS NULL из прошлого поста. В нашем случае - это статья "Активы", т.к. у нее нет родительского id, потому что это самая "старшая" статья. На данном этапе CTE "tree" содержит только одну строку - это статью "Активы" с account_id = 1 и parent_id IS NULL. 2️⃣Во втором SELECT берется просто таблица accounts, к которой джойнится результат из первого SELECT по parent_id = account_id, то есть со смещением. Иными словами, статья "Активы" с account_id = 1 присоединяется к статьям "Дебиторская задолженность" и "Денежные средства" с parent_id = 1. 3️⃣После этого результаты селектов объединяются с помощью UNION ALL. На данном этапе CTE "tree" содержит уже 3 строки - это статью "Дебиторская задолженность" и "Денежные средства" + ранее полученные "Активы". 4️⃣И сейчас запускается рекурсия. Причем запускается она не с первого SELECT - там ей просто не от куда взяться. Запускается она со второго, потому что в нем присутствует JOIN tree, который уже содержит 3 строки. И снова выбираются данные из accounts, снова к ним со смещением присоединяются строки из "tree", и в итоге получаем уже набор из всех строк (в нашем случае) 5️⃣Заканчивается рекурсия на том моменте, когда между accounts и tree уже нет совпадений и результат ничего не выдает (потому что INNER JOIN). 🟠В итоге получаем таблицу в виде таблицы accounts, только упорядоченную по иерархии и с добавлением уровня иерархии (поле level).