PostgreSQL - Рекурсивный запрос

PostgreSQL предоставляет оператор WITH, который поддерживает создание вспомогательных запросов, также известных как CTE (общие табличные выражения). Рекурсивный запрос - это запрос, который ссылается на рекурсивный CTE. Рекурсивные запросы полезны во многих случаях, например, для запроса иерархических данных, таких как организационная структура, отслеживание происхождения и т. Д.

Синтаксис:

 С РЕКУРСИВНЫМ cte_name AS (
CTE_query_definition <- нерекурсивный термин
СОЮЗ [ВСЕ]
CTE_query definition <- рекурсивный термин
) ВЫБРАТЬ * ИЗ cte_name;

Давайте проанализируем приведенный выше синтаксис:

• Нерекурсивный термин - это определение запроса CTE, которое формирует базовый набор результатов структуры CTE.
• Рекурсивный термин может быть одним или несколькими определениями запроса CTE, соединенными с нерекурсивным термином с помощью оператора UNION или UNION ALL. Рекурсивный термин ссылается на само имя CTE.
• Рекурсия останавливается, когда с предыдущей итерации не возвращаются строки.

Сначала мы создаем образец таблицы, используя следующие команды для выполнения примеров:

 СОЗДАТЬ ТАБЛИЦУ сотрудников (
    employee_id серийный ПЕРВИЧНЫЙ КЛЮЧ,
    полное_имя VARCHAR NOT NULL,
    manager_id INT
);

Затем мы вставляем данные в нашу таблицу сотрудников следующим образом:

INSERT INTO employees (
    employee_id,
    full_name,
    manager_id
)
VALUES
    (1, "M.S Dhoni", NULL),
    (2, "Sachin Tendulkar", 1),
    (3, "R. Sharma", 1),
    (4, "S. Raina", 1),
    (5, "B. Kumar", 1),
    (6, "Y. Singh", 2),
    (7, "Virender Sehwag ", 2),
    (8, "Ajinkya Rahane", 2),
    (9, "Shikhar Dhawan", 2),
    (10, "Mohammed Shami", 3),
    (11, "Shreyas Iyer", 3),
    (12, "Mayank Agarwal", 3),
    (13, "K. L. Rahul", 3),
    (14, "Hardik Pandya", 4),
    (15, "Dinesh Karthik", 4),
    (16, "Jasprit Bumrah", 7),
    (17, "Kuldeep Yadav", 7),
    (18, "Yuzvendra Chahal", 8),
    (19, "Rishabh Pant", 8),
    (20, "Sanju Samson", 8);

Теперь, когда таблица готова, мы можем рассмотреть несколько примеров.

Пример 1:
Приведенный ниже запрос возвращает всех подчиненных менеджера с идентификатором 3.

 С РЕКУРСИВНЫМИ подчиненными AS (
    ВЫБРАТЬ
        employee_id,
        manager_id,
        полное имя
    ИЗ
        сотрудники
    КУДА
        employee_id = 3
    СОЮЗ
        ВЫБРАТЬ
            e.employee_id,
            e.manager_id,
            e.full_name
        ИЗ
            сотрудники е
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ подчиненных s НА s.employee_id = e.manager_id
) ВЫБРАТЬ
    *
ИЗ
    подчиненные;

Выход:

Пример 2:
Приведенный ниже запрос возвращает всех подчиненных менеджера с идентификатором 4.

 С РЕКУРСИВНЫМИ подчиненными AS (
    ВЫБРАТЬ
        employee_id,
        manager_id,
        полное имя
    ИЗ
        сотрудники
    КУДА
        employee_id = 4
    СОЮЗ
        ВЫБРАТЬ
            e.employee_id,
            e.manager_id,
            e.full_name
        ИЗ
            сотрудники е
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ подчиненных s НА s.employee_id = e.manager_id
) ВЫБРАТЬ
    *
ИЗ
    подчиненные;

Выход: