Следующий шаг - поместить состоящее из отдельных групп результирующее множество во вложенный запрос и вычислить текущую сумму по FLAG1 (наконец его предназначение раскрыто!), который будет играть роль ранга для каждой строки в каждой группе. Результаты ранжирования (текущего суммирования) показаны ниже:

select sum(flag1)over(partition by flag2

order by flag1,rownum) flag,

it dept, flag2

from (

select 1 flag1, 0 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 research

) x,

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

where rn <= cnt+1 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 deptno, ename,

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

) x,

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

where rn <= cnt+1 ) tmp1

FLAG IT DEPT FLAG2

1 100 0

2 JONES 0

3 TONEY 0

4 HOPKINS 0

5 200 0

6 P.WHITAKER 0

7 MARCIANO 0

8 ROBINSON 0

9 MORALES 0

И последний шаг (наконец!) - развернуть значения, возвращаемые TMP1, по FLAG2, группируя при этом по FLAG (текущей сумме, сгенерированной в TMP1). Результаты TMP1 помещаются во вложенный запрос и разворачиваются (все это оформлено как окончательный вложенный запрос TMP2). Окончательное решение и результирующее множество показаны ниже:

select max(decode(flag2,0,it dept)) research, max(decode(flag2,1,it dept)) apps from (

select sum(flag1)over(partition by flag2

order by flag1,rownum) flag,

it dept, flag2

from (

select 1 flag1, 0 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 research

) x,

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

where rn <= cnt+1 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 deptno, ename,

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

) x,

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

where rn <= cnt+1

) tmp1

) tmp2 group by flag

RESEARCH APPS

100 JONES TONEY HOPKINS

P.WHITAKER MARCIANO ROBINSON MORALES 300 WRIGHT J.TAYLOR LACY

400 MAYWEATHER CASTILLO MARQUEZ MOSLEY CORRALES

CALZAGHE

GATTI 600

HAGLER

HEARNS

FRAZIER

LAMOTTA 700

JUDAH

MARGARITO

GUINN

Преобразование скалярного подзапроса в составной подзапрос (Oracle)

Задача

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

select e.deptno, e.ename,

e.sal,