Генерация списка чисел 1-100 через DUAL в SQL: подробный гайд

Быстрый ответ

Для создания последовательности чисел от 1 до 100 в SQL вы можете использовать рекурсивное общее табличное выражение (CTE):

WITH Numbers AS (
    SELECT 1 AS Value
    UNION ALL
    SELECT Value + 1 FROM Numbers WHERE Value < 100
)
SELECT Value FROM Numbers OPTION (MAXRECURSION 100);

Этот компактный код подойдёт, если необходимо оперативно сгенерировать числовую последовательность с использованием рекурсии.

Специфика работы с Oracle

Иерархический метод: CONNECT BY LEVEL

Oracle предлагает использовать CONNECT BY LEVEL для генерации последовательности чисел:

SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100;

В системе Oracle встроенная колонка LEVEL автоматически увеличивается на каждой итерации запроса к виртуальной таблице DUAL.

Подробнее об этом расскажет наш спикер на видео

Рекурсивный подзапрос: WITH-конструкция

Oracle также поддерживает использование рекурсивных WITH-конструкций:

WITH Numbers (n) AS (
    SELECT 1 FROM DUAL
    UNION ALL
    SELECT n + 1 FROM Numbers WHERE n < 100
)
SELECT n FROM Numbers;

Полученный результат идентичен использованию CTE в представленном в быстром ответе примере, но данный метод адаптирован под особенности работы с Oracle.

Необычные подходы

XMLTABLE: Нестандартный метод

Использование XMLTABLE может быть весьма нестандартным решением:

SELECT * FROM XMLTABLE('for $i in 1 to 100 return $i');

Данный метод использует COLUMN_VALUE и с использованием XML формирует числовую последовательность.

MODEL-конструкция: Последовательная генерация чисел

Oracle предлагает использовать MODEL-конструкцию для создания последовательности чисел:

SELECT * FROM (
  SELECT 0 N FROM DUAL
) MODEL DIMENSION BY (N) MEASURES (N) RULES ITERATE(100) (N[ITERATION_NUMBER]=ITERATION_NUMBER+1);

Сущности заполняются числами в порядке возрастания от 1 до 100.

Визуализация

Представьте себя шеф-поваром, которому на банкет требуется 100 яиц. SQL может помочь вам с подсчетом:

WITH EggCarton (Number) AS (
    SELECT 1 UNION ALL
    SELECT Number + 1 FROM EggCarton WHERE Number < 100
)
SELECT Number FROM EggCarton OPTION (MAXRECURSION 100);

Каждый рекурсивный шаг можно представить как процесс постановки яйца в контейнер до тех пор, пока не будет достигнуто нужное количество.

Коробка с яйцами (от 1 до 100): 🥚🥚🥚 ... до тех пор, пока количество яиц не достигнет 100

Такой сценарий позволит мастеру-повару учесть каждое яйцо.

Работа с различными СУБД

PostgreSQL: Встроенная функция

В PostgreSQL есть функция generate_series для простой генерации последовательностей:

SELECT * FROM generate_series(1, 100);

MySQL: DUAL — недооцененный "герой"

В MySQL можно создать числовую последовательность, используя переменные и DUAL:

SELECT @row := @row + 1 as Number
FROM (
  SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
) t
CROSS JOIN (
  SELECT @row := 0
) r
LIMIT 100;

SQL Server: классический подход

Как было показано в быстром ответе, SQL Server может использовать рекурсивные CTE. Важно контролировать опцию MAXRECURSION.

Лучшие практики безопасности при выполнении запросов

Не забывайте:

• Использовать ORDER BY для упорядочивания чисел.
• Учитывать ограничения ресурсов и компромиссы в производительности.
• Искать методы оптимизации, специфичные для вашей СУБД.

Полезные материалы

1. SQL – generate days from a date range – Stack Overflow — как создать последовательность дат в SQL Server.
2. The "Numbers" or "Tally" Table: What it is and how it replaces a loop – SQLServerCentral — замена циклов в SQL на использование "табличных" чисел.
3. Иерархические запросы – документация Oracle — об иерархических числовых последовательностях в Oracle.
4. Документация PostgreSQL: 9.25. Функции возвращения набора — о функции generate_series в PostgreSQL.
5. Руководство по MySQL 8.0: Оператор SELECT — как MySQL использует DUAL для генерации числовых последовательностей.
6. Документация IBM — методы генерации чисел в Db2.
7. SQL Performance: Создайте набор или последовательность без циклов – SQLPerformance.com — о техниках создания наборов в SQL.