На главную страницу
Генерация числовой последовательности
Моисеенко С.И.
(тема предложена 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.
На главную страницу