Рекурсивные подзапросы

Рекурсивный запрос посложнее

Разберем пример, когда в нерекурсивной части несколько строк. Построим наборы чисел от 100 до 103 и от 200 до 201:

WITH RECURSIVE lv_initial AS (
    SELECT 100 AS start_number,
           4 AS count_iterations
     UNION ALL
    SELECT 200 AS start_number,
           2 AS count_iterations
), 
lv_numbers (start_number, count_iterations, current_iteration, result_number) AS (
    SELECT start_number,
           count_iterations,
           1 AS current_iteration,
           start_number AS result_number
      FROM lv_initial
     UNION ALL
    SELECT p.start_number,
           p.count_iterations,
           p.current_iteration + 1,
           p.result_number + 1
      FROM lv_numbers p
     WHERE p.current_iteration < p.count_iterations
)
SELECT n.start_number,
       n.result_number
  FROM lv_numbers n
 ORDER BY n.start_number,
          n.result_number
#start_numberresult_number
1100100
2100101
3100102
4100103
5200200
6200201

Обрати внимание, ключевое слово RECURSIVE стоит не перед рекурсивным подзапросом, а сразу после WITH.

Как выполняется запрос

Шаг 1. Выполняется нерекурсивная часть подзапроса.

Таблица lv_initial:

#start_numbercount_iterations
11004
22002

Результат нерекурсивной части:

#start_numbercount_iterationscurrent_iterationresult_number
110041100
220021200

Временная таблица:

#start_numbercount_iterationscurrent_iterationresult_number
110041100
220021200

Шаг 2. Рекурсивная часть выполняется 1-й раз

Начальная временная таблица

#start_numbercount_iterationscurrent_iterationresult_number
110041100
220021200

Результат подзапроса

    SELECT p.start_number,
           p.count_iterations,
           p.current_iteration + 1,
           p.result_number + 1
      FROM lv_numbers p
     WHERE p.current_iteration < p.count_iterations
#start_numbercount_iterationscurrent_iterationresult_number
110042101
220022201

Общий результат запроса

#start_numbercount_iterationscurrent_iterationresult_number
110041100
220021200
310042101
420022201

Временная таблица

#start_numbercount_iterationscurrent_iterationresult_number
110042101
220022201

Шаг 3. Рекурсивная часть выполняется 2-й раз

Начальная временная таблица

#start_numbercount_iterationscurrent_iterationresult_number
110042101
220022201

Результат подзапроса

    SELECT p.start_number,
           p.count_iterations,
           p.current_iteration + 1,
           p.result_number + 1
      FROM lv_numbers p
     WHERE p.current_iteration < p.count_iterations
#start_numbercount_iterationscurrent_iterationresult_number
110043102

Общий результат запроса

#start_numbercount_iterationscurrent_iterationresult_number
110041100
220021200
310042101
420022201
510043102

Временная таблица

#start_numbercount_iterationscurrent_iterationresult_number
110043102

Шаг 4. Рекурсивная часть выполняется 3-й раз

Начальная временная таблица

#start_numbercount_iterationscurrent_iterationresult_number
110043102

Результат подзапроса

    SELECT p.start_number,
           p.count_iterations,
           p.current_iteration + 1,
           p.result_number + 1
      FROM lv_numbers p
     WHERE p.current_iteration < p.count_iterations
#start_numbercount_iterationscurrent_iterationresult_number
110044103

Общий результат запроса

#start_numbercount_iterationscurrent_iterationresult_number
110041100
220021200
310042101
420022201
510043102
610044103

Временная таблица

#start_numbercount_iterationscurrent_iterationresult_number
110044103

Шаг 5. Рекурсивная часть выполняется 4-й раз

Начальная временная таблица

#start_numbercount_iterationscurrent_iterationresult_number
110044103

Результат подзапроса

    SELECT p.start_number,
           p.count_iterations,
           p.current_iteration + 1,
           p.result_number + 1
      FROM lv_numbers p
     WHERE p.current_iteration < p.count_iterations
0 строк

Общий результат запроса

#start_numbercount_iterationscurrent_iterationresult_number
110041100
220021200
310042101
420022201
510043102
610044103

Временная таблица пуста. На этом рекурсивная часть запроса завершает выполнение.

Шаг 6. Выполняется основной запрос

SELECT n.start_number,
       n.result_number
  FROM lv_numbers n
 ORDER BY n.start_number,
          n.result_number
#start_numberresult_number
1100100
2100101
3100102
4100103
5200200
6200201
ПредыдущаяСледующая