Статьи

Как создать диапазон от 1 до 10 в SQL

Март 22, 2018

Как создать диапазон от 1 до 10 в SQL? Вы когда-нибудь думали об этом? Это такая простая задача, которую можно решить на любом императивном языке, это смешно. Возьмите Java (или C, что угодно) например:

for (int i = 1; i <= 10; i++)
  System.out.println(i);

Это было легко, правда? Вещи даже выглядят более скудными при использовании функционального программирования. Взять, к примеру, Scala:

(1 to 10) foreach { t => println(t) }

Мы могли бы заполнить около 25 страниц о различных способах выполнения вышеперечисленного в Scala , соглашаясь с тем, какой класс Scala ( или какие мы хипстеры ).

Но как создать диапазон в SQL?

… И мы исключим использование хранимых процедур, потому что это было бы неинтересно. В SQL источником данных, над которым мы работаем, являются таблицы. Если нам нужен диапазон от 1 до 10, нам, вероятно, понадобится таблица, содержащая именно эти десять значений. Вот несколько хороших, плохих и уродливых вариантов сделать именно это в SQL. ОК, они в основном плохие и уродливые.

Создавая таблицу

Самый глупый способ сделать это — создать временную таблицу только для этой цели:

CREATE TABLE "1 to 10" AS
SELECT 1 value FROM DUAL UNION ALL
SELECT 2       FROM DUAL UNION ALL
SELECT 3       FROM DUAL UNION ALL
SELECT 4       FROM DUAL UNION ALL
SELECT 5       FROM DUAL UNION ALL
SELECT 6       FROM DUAL UNION ALL
SELECT 7       FROM DUAL UNION ALL
SELECT 8       FROM DUAL UNION ALL
SELECT 9       FROM DUAL UNION ALL
SELECT 10      FROM DUAL

Смотрите также этот SQLFiddle

Эта таблица может затем использоваться в любом типе выбора. Это довольно глупо, но просто, верно? Я имею в виду, сколько реальных записей вы собираетесь поместить туда?

Используя конструктор таблицы VALUES ()

Это решение не намного лучше. Вы можете создать производную таблицу и вручную добавить значения от 1 до 10 в эту производную таблицу, используя VALUES()конструктор таблицы. В SQL Server вы могли бы написать:

SELECT V
FROM (
  VALUES (1), (2), (3), (4), (5),
         (6), (7), (8), (9), (10)
) [1 to 10](V)

Смотрите также этот SQLFiddle

Создавая достаточное количество самостоятельных соединений с достаточным количеством значений

Другим «тупым», но немного более общим решением было бы создание только определенного количества постоянных значений в таблице, представлении или CTE (например, два), а затем самостоятельное присоединение к этой таблице достаточное количество раз, чтобы достичь желаемой длины диапазона (например, четыре раз). Следующий пример выдаст значения от 1 до 10 «легко»:

WITH T(V) AS (
  SELECT 0 FROM DUAL UNION ALL
  SELECT 1 FROM DUAL
)
SELECT V FROM (
  SELECT 1        +
             T1.V +
         2 * T2.V +
         4 * T3.V +
         8 * T4.V V
  FROM T T1, T T2, T T3, T T4
)
WHERE V <= 10
ORDER BY V

Смотрите также этот SQLFiddle

Используя наборы группировки

Другой способ создания больших таблиц — использование группирующих наборов или, более конкретно, использование CUBE()функции. Это работает так же, как и в предыдущем примере, при самостоятельном объединении таблицы с двумя записями:

SELECT ROWNUM FROM (
  SELECT 1
  FROM DUAL
  GROUP BY CUBE(1, 2, 3, 4)
)
WHERE ROWNUM <= 10

Смотрите также этот SQLFiddle

Просто беря случайные записи из «достаточно большой» таблицы

В Oracle вы, вероятно, могли бы использовать ALL_OBJECTs. Если вы рассчитываете только до 10, вы наверняка получите достаточно результатов из этой таблицы:

SELECT ROWNUM FROM ALL_OBJECTS
WHERE ROWNUM <= 10

Смотрите также этот SQLFiddle

Что удивительного в этом решении, так это то, что вы можете перекрестно соединять эту таблицу несколько раз, чтобы быть уверенным, что получите достаточно значений:

SELECT ROWNUM
FROM ALL_OBJECTS, ALL_OBJECTS,
     ALL_OBJECTS, ALL_OBJECTS
WHERE ROWNUM <= 10

ХОРОШО. Просто шучу. На самом деле не делай этого. Или, если вы это сделаете, не вините меня, если ваша производительная система не хватает памяти.

С помощью потрясающей функции PostgreSQL GENERATE_SERIES ()

Невероятно, но это не является частью стандарта SQL. Он также недоступен в большинстве баз данных, кроме PostgreSQL, который имеет эту GENERATE_SERIES()функцию. Это очень похоже на обозначение диапазона в Scala:(1 to 10)

SELECT * FROM GENERATE_SERIES(1, 10)

Смотрите также этот SQLFiddle

Используя CONNECT BY

Если вы используете Oracle, то существует действительно простой способ создать такую ​​таблицу с помощью CONNECT BYпредложения, что почти так же удобно, как GENERATE_SERIES()функция PostgreSQL :

SELECT LEVEL FROM DUAL
CONNECT BY LEVEL < 10

Смотрите также этот SQLFiddle

Используя рекурсивный CTE

Рекурсивные общие табличные выражения — это круто, но совершенно нечитаемо. Эквивалент вышеупомянутого предложения Oracle CONNECT BY при написании с использованием рекурсивного CTE будет выглядеть так:

WITH "1 to 10"(V) AS (
  SELECT 1 FROM DUAL
  UNION ALL
  SELECT V + 1 FROM "1 to 10"
  WHERE V < 10
)
SELECT * FROM "1 to 10"

Смотрите также этот SQLFiddle

Используя предложение Oracle MODEL

Достойное «лучшее из» сравнения того, как работать в SQL, не было бы полным без хотя бы одного примера, использующего предложение Oracle MODEL ( см. Этот удивительный пример использования функции Oracle для работы с электронными таблицами ). Используйте этот пункт только для того, чтобы ваши коллеги действительно разозлились, поддерживая ваш код SQL.

Поклонись этой красавице!

SELECT V
FROM (
  SELECT 1 V FROM DUAL
) T
MODEL DIMENSION BY (ROWNUM R)
      MEASURES (V)
      RULES ITERATE (10) (
        V[ITERATION_NUMBER] = CV(R) + 1
      )
ORDER BY 1

Смотрите также этот SQLFiddle

Вывод

На самом деле не так много хороших решений для такой простой вещи в SQL. Очевидно, что GENERATE_SERIES()табличная функция PostgreSQL — самое красивое решение. CONNECT BYПредложение Оракула приближается. Для всех других баз данных некоторые хитрости должны быть применены тем или иным способом.

К несчастью.

 

369
Share: