year(min(hiredate)over()) min year, mod(year(min(hiredate)over()),10) mod yr, row number()over()-1 rn from emp fetch first 10 rows only

select min year-mod(min year,10)+rn as yr, min year,

mod(min year,10) as mod yr rn

from (

select (select min(extract(year from hiredate)) from emp) as min year, id-1 as rn

from t10 ) x

YR MIN YEAR

MOD YR

1980 1981 1982 1983 1984 1985 1986 1987 1988

1989

1980 1980

1980 1980

1980 1980

1980

1980 1980

1980

Вложенный запрос Y возвращает годы, соответствующие значениям HIREDATE, и количество служащих, принятых на работу в течение каждого из них:

select year(hiredate) yr, count(*) cnt from emp group by year(hiredate)

1980 1981

1982 2

1983 1

Для получения окончательного решения выполняем внешнее объединение вложенного запроса Y с вложенным запросом Х, чтобы в результирующем множестве получить все годы десятилетия, даже те, в течение которых ни один служащий не был принят на работу.

Формирование последовательности числовых значений

Задача

Хотелось бы иметь в своем распоряжении генератор строк . Генераторы строк пригодятся для запросов, в которых требуется выполнить разворачивание. Например, стоит задача получить результирующее множество, подобное приведенному ниже, с любым заданным количеством строк:

2 3 4

8 9 10

Если СУБД предоставляет встроенные функции для динамического формирования строк, в предварительном создании сводной таблицы с фиксированным числом строк нет необходимости. В этом и состоит преимущество динамического генератора строк. В противном случае, если требуется получить определенное количество строк, приходится использовать традиционную сводную таблицу (и этого не всегда достаточно).

Решение

В данном решении показано, как получить 10 строк, в которых располагаются числа по возрастанию, начиная с 1. Это решение можно без труда адаптировать для получения любого количества строк.

Возможность получать ряд последовательных возрастающих значений, начиная с 1, открывает путь ко многим другим решениям. Например, можно генерировать числа и, добавляя их к датам, получать последовательности дней. Кроме того, можно использовать такие ряды чисел для синтаксического разбора строк.

Далее приведено второе, альтернативное решение, подходящее только для DB2. Его преимущество состоит в том, что в нем не требуется таблица T1:1

Oracle

Используйте рекурсивный оператор CONNECT BY (Oracle 9i Database или более поздние версии). В Oracle 9i Database необходимо поместить решение с применением CONNECT BY в конструкцию WITH:

1 with x

2 as (

3 select level id

4 from dual

5 connect by level <= 10

7 select * from x

В Oracle Database 10g или более поздних версиях можно формировать строки с помощью оператора MODEL:

1 select array id

2 from dual

Для SQL Server также можно обойтись без таблицы T1, для этого достаточно опустить строку 4 в общем решении. - Примеч. науч.ред.

DB2 и SQL Server

Для формирования последовательности строк с возрастающими значениями используйте рекурсивный оператор WITH. В качестве отправной точки для формирования строк используйте таблицу T1, содержащую одну строку; все остальное сделает оператор WITH: