Книги и статьи по SQL Rambler's Top100 Switch language to: English 14 июля 2020 г. 18:43:46


www.sql-ex.ru
Skip Navigation Links  

 

Print  Версия для печати

На главную страницу

Генерация числовой последовательности

Моисеенко С.И.
(тема предложена Shurgenz)

Иногда возникает необходимость получить в запросе числовую последовательность. Это может быть самоцелью или же промежуточным результатом для получения, скажем, последовательности дат. Пусть, например, требуется получить последовательность целых чисел от 1 до 100 с шагом 1. Можно, конечно, строить такую последовательность в "лоб", т.е.

SELECT 1 
  UNION ALL SELECT 2
   ...
    UNION ALL SELECT 100

А если потребуется 1000 чисел? А ведь может еще существовать ограничение на размер запроса.

Помочь может декартово произведение (CROSS JOIN), которое редко когда используется, разве что в качестве промежуточного результата. Существенной особенностью декартова произведения является то, что мощность результата (количество строк) равно произведению мощностей участвующих в декартовом произведении таблиц. Т.е. если нам нужно получить последовательность 100 чисел мы можем использовать декартово произведение таблиц, каждая из которых содержит по 10 записей. Итак,

SELECT * FROM
   (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
      UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) x
CROSS JOIN
   (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
      UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) y

Результатом здесь является двухстолбцовая таблица, содержащая 100 строк. При этом каждое значение из первого подзапроса (числа от 1 до 10) сочетается с каждым значением из второго (аналогичного) подзапроса:

a b
1 1
1 2
...
1 10
2 1
2 2
...
2 10
...

Теперь осталось только вычислить сами значения. Будем считать, скажем, что число в первом столбце представляет собой десятки -1, а второй - единицы. Тогда вместо SELECT * FROM … в нашем запросе напишем

SELECT 10*(a-1)+b FROM …

что и даст нам требуемый результат.

А почему бы ни взять 3 таблицы (подзапроса)? Чем больше размер генерируемой последовательности, тем больше таблиц следует взять, чтобы получить более короткий запрос. Аналогично рассуждая и, исходя из того, что 5*5*5 = 125, получим:

SELECT 5*5*(a-1)+5*(b-1) + c AS num FROM
   (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) x
   CROSS JOIN
   (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) y
   CROSS JOIN
   (SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) z
      WHERE 5*5*(a-1)+5*(b-1) + c <=100
      ORDER BY 1

Условие WHERE 5*5*(a-1)+5*(b-1) + c <=100 использовано для того, чтобы ограничить последовательность значением 100, а не 125.

Рассмотрим "практический" пример. Пусть требуется получить 100 последующих незанятых номеров моделей на основе таблицы Product. Идея такова: находим максимальный номер модели и далее, используя генерацию последовательности, 100 последующих значений с шагом 1.

SELECT (SELECT MAX(model) FROM Product) + 5*5*(a-1)+5*(b-1) + c AS num FROM
   (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) x
   CROSS JOIN
   (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) y
   CROSS JOIN
   (SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) z
      WHERE 5*5*(a-1)+5*(b-1) + c <=100
      ORDER BY 1

Результат выполнения этого запроса я не буду приводить здесь из экономии килобайт. Проверьте сами.

Приведенные здесь примеры можно выполнить непосредственно на сайте, установив флажок "Без проверки" на странице с упражнениями на SELECT.

На главную страницу

Print  Версия для печати


Использование любых материалов данного сайта возможно только
при условии обязательного размещения прямой ссылки на сайт
http://www.sqlbooks.ru
на каждой странице, где размещены используемые материалы.

 Начало   Статьи    Книги 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.