Генерация списка чисел 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
для упорядочивания чисел. - Учитывать ограничения ресурсов и компромиссы в производительности.
- Искать методы оптимизации, специфичные для вашей СУБД.
Полезные материалы
- SQL – generate days from a date range – Stack Overflow — как создать последовательность дат в SQL Server.
- The "Numbers" or "Tally" Table: What it is and how it replaces a loop – SQLServerCentral — замена циклов в SQL на использование "табличных" чисел.
- Иерархические запросы – документация Oracle — об иерархических числовых последовательностях в Oracle.
- Документация PostgreSQL: 9.25. Функции возвращения набора — о функции generate_series в PostgreSQL.
- Руководство по MySQL 8.0: Оператор SELECT — как MySQL использует DUAL для генерации числовых последовательностей.
- Документация IBM — методы генерации чисел в Db2.
- SQL Performance: Создайте набор или последовательность без циклов – SQLPerformance.com — о техниках создания наборов в SQL.