Решение

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

Используя таблицу EMP как сводную таблицу (поскольку в ней 14 строк) и встроенную функцию YEAR, сгенерируйте по строке для каждого года десятилетия 1980-х. Выполните внешнее объединение с таблицей EMP и подсчитайте, по сколько служащих устраивалось на работу каждый год:

Используя таблицу EMP как сводную таблицу (поскольку в ней 14 строк) и встроенные функции TONUMBER и TOCHAR, сгенери-

руйте по строке для каждого года десятилетия 1980-х. Выполните внешнее объединение с таблицей EMP и подсчитайте, по сколько служащих устраивалось на работу каждый год:

При работе с Oracle 9i Database или более поздними версиями можно реализовать решение, используя новый оператор JOIN:

1 select x.yr, coalesce(cnt,0) cnt

2 from (

3 select extract(year from min(hiredate)over()) -

4 mod(extract(year from min(hiredate)over()),10) +

5 rownum-1 yr

6 from emp

7 where rownum <= 10

8 ) x

9 left join

10 (

11 select to number(to char(hiredate,YYYY)) yr, count(*) cnt

12 from emp

13 group by to number(to char(hiredate,YYYY))

14 ) y

15 on ( x.yr = y.yr )

PostgreSQL и MySQL

Используя таблицу T10 как сводную таблицу (поскольку в ней 10 строк) и встроенную функцию EXTRACT, сгенерируйте по строке для каждого года десятилетия 1980-х. Выполните внешнее объединение с таблицей EMP и подсчитайте, по сколько служащих устраивалось на работу каждый год:

1 select y.yr, coalesce(x.cnt,0) as cnt

2 from (

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

4 from (

5 select (select min(extract(year from hiredate))

6 from emp) as min year,

7 id-1 as rn

Используя таблицу EMP как сводную таблицу (поскольку в ней 14 строк) и встроенную функцию YEAR, сгенерируйте по строке для каждого года десятилетия 1980-х. Выполните внешнее объединение с таблицей EMP и подсчитайте, по сколько служащих устраивалось на работу каждый год:

Обсуждение

Несмотря на различия в синтаксисе, во всех решениях подход один и тот же. Вложенный запрос Х находит, в каком году десятилетия 80-х был принят первый служащий (минимальное значение HIREDATE). Следующий шаг - к разности между годом первого приема на работу и остатком от деления этого года на 10 добавляется RN - 1. Чтобы разобраться, просто выполните вложенный запрос Х и получите каждое из участвующих в этой операции значений по отдельности. Ниже представлено результирующее множество вложенного запроса Х, использующего оконную функцию MIN OVER (DB2, Oracle, SQL Server) и скалярный подзапрос (MySQL, PostgreSQL):

select year(min(hiredate)over()) -

mod(year(min(hiredate)over()),10) + row number()over()-1 yr,