34 (select level id from dual connect by level <= 2) y

35 )

36 where rn <= cnt+1

37 ) tmp1

38 ) tmp2

39 group by flag

Обсуждение

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

select 1 flag1, 1 flag2,

decode(rn,1,to char(deptno), ename) it dept from ( select x.*, y.id,

row number()over(partition by x.deptno order by y.id) rn from ( select deptno, ename,

count(*)over(partition by deptno) cnt from it apps

) x,

(select level id from dual connect by level <= 2) y ) z

where rn <= cnt+1

FLAG1 FLAG2 IT DEPT

1 1 400

1 1 MAYWEATHER

1 1 CASTILLO

1 1 MARQUEZ

1 1 MOSLEY

1 1 CORRALES

1 1 500

1 1 CALZAGHE

1 1 GATTI

1 1 600

1 1 HAGLER

1 1 HEARNS

1 1 FRAZIER

1 1 LAMOTTA

1 1 700

1 1 JUDAH

1 1 MARGARITO

1 1 GUINN

Рассмотрим, как именно образуется результирующее множество. Разложив представленный выше запрос на его простейшие компоненты, получаем вложенный запрос Х, который просто возвращает из таблицы IT APPS все значения ENAME и DEPTNO и количество служащих в каждом отделе. Результаты таковы:

Следующий шаг - создание декартова произведения строк, возвращенных вложенным запросом Х, и двух строк, сформированных с использованием CONNECT BY из DUAL. Результаты этой операции приведены ниже:

select * from ( select deptno deptno, ename,

count(*)over(partition by deptno) cnt from it apps

) x,

(select level id from dual connect by level <= 2) y order by 2

DEPTNO ENAME

CNT ID

500 CALZAGHE

500 CALZAGHE

400 CASTILLO

400 CASTILLO

400 CORRALES

400 CORRALES

600 FRAZIER

600 FRAZIER

Как видим, теперь в результате декартова произведения с вложенным запросом Y каждая строка вложенного запроса Х возвращена дважды. Зачем необходимо декартово произведение, вскоре станет ясно. Следующий шаг - ранжировать всех служащих полученного результирующего множества в рамках каждого DEPTNO по ID (ID в результате декартова произведения имеет значения 1 или 2). Результат этого ранжирования показан в выводе следующего запроса:

select x.*, y.id,

row number()over(partition by x.deptno order by y.id) rn from ( select deptno deptno, ename,

count(*)over(partition by deptno) cnt from it apps

) x,

(select level id from dual connect by level <= 2) y