loading..
Русский    English
10:01
листать

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

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

  1. SELECT 1 AS num
  2. UNION ALL 
  3. SELECT 2
  4. ...
  5. UNION ALL 
  6. SELECT 100;

А если потребуется 1000 чисел или больше? Помимо затрат времени на написание такого количества операторов, мы будем неэффективно использовать сетевой трафик, передавая на выполнение серверу запросы такого размера.

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

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

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

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

  1. SELECT 10*(a-1)+b 
  2. FROM …
что и даст требуемый результат.

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

Консоль
Выполнить
  1. SELECT 5*5*(a-1)+5*(b-1) + c AS num 
  2. FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 
  3.  UNION ALL SELECT 4 UNION ALL SELECT 5
  4.  ) x CROSS JOIN
  5.  (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 
  6.  UNION ALL SELECT 4 UNION ALL SELECT 5
  7.  ) y CROSS JOIN
  8.  (SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 
  9.  UNION ALL SELECT 4 UNION ALL SELECT 5
  10.  ) z
  11. WHERE 5*5*(a-1)+5*(b-1) + c <= 100
  12. ORDER BY 1;

Условие

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

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

Консоль
Выполнить
  1. SELECT (SELECT MAX(model) 
  2.  FROM Product
  3.  ) + 5*5*(a-1)+5*(b-1) + c AS num 
  4. FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 
  5.  UNION ALL SELECT 4 UNION ALL SELECT 5
  6.  ) x CROSS JOIN
  7.  (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 
  8.  UNION ALL SELECT 4 UNION ALL SELECT 5
  9.  ) y CROSS JOIN
  10.  (SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 
  11.  UNION ALL SELECT 4 UNION ALL SELECT 5
  12.  ) z
  13. WHERE 5*5*(a-1)+5*(b-1) + c <= 100
  14. ORDER BY 1;

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

Если ваш сервер поддерживает CTE, то получение числовой последовательности существенно упрощается. Вы можете использовать Консоль учебника, чтобы решить рассмотренную здесь задачу  этим способом. За примерами вы можете обратиться к главе, посвященной рекурсивным  CTE (общее табличное выражение) позволяет в рамках запроса задать таблицу, на которую можно многократно ссылаться.CTE, и где рассматривается несколько числовых последовательностей.

Рекомендуемые упражнения: 66, 94, 104, 118, 143
Страницы: 1 2
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INFORMATION_SCHEMA INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker Больше тэгов
Учебник обновлялся
несколько дней назад
pedant-ivanovo.ru
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.