empno, ename from emp

Следующий и последний шаг - посредством выражения CASE обеспечить однократное отображение DEPTNO. Если значение EMPNO служащего соответствует MIN EMPNO, возвращается DEPTNO; в противном случае возвращается NULL:

select case when empno=min empno then deptno else null end deptno, ename from ( select deptno,

min(empno)over(partition by deptno) min empno, empno, ename from emp ) x

DEPTNO ENAME

10 CLARK

KING

MILLER 20 SMITH

ADAMS

FORD

SCOTT

JONES 30 ALLEN

BLAKE

MARTIN

JAMES

TURNER

WARD

Oracle

Первый шаг - с помощью оконной функции LAG OVER возвратить предыдущее значение DEPTNO для каждой строки:

select lag(deptno)over(order by deptno) lag deptno, deptno, ename from emp

LAG DEPTNO DEPTNO ENAME 10 CLARK

10 10 KING

10 10 MILLER

10 20 SMITH

20 20 ADAMS

20 20 FORD

20 20 SCOTT

20 20 JONES

20 30 ALLEN

30 30 BLAKE

30 30 MARTIN

30 30 JAMES

30 30 TURNER

30 30 WARD

Взглянув на представленное выше результирующее множество, можно сразу увидеть, где значения DEPTNO и LAG DEPTNO совпадают. Для этих строк DEPTNO должно быть присвоено значение NULL. Делается это с помощью функции DECODE (функция TO NUMBER включена, чтобы привести значение DEPTNO к числовому типу):

select to number(

decode(lag(deptno)over(order by deptno), deptno,null,deptno) ) deptno, ename from emp

DEPTNO ENAME

10 CLARK

KING

MILLER 20 SMITH

ADAMS

FORD

SCOTT

JONES 30 ALLEN

Обсуждение

Первый шаг - посредством выражений CASE развернуть таблицу и перенести заработные платы для каждого DEPTNO из строк в столбцы:

BLAKE

MARTIN

JAMES

TURNER

WARD

Разворачивание результирующего множества для упрощения вычислений

Задача

Требуется выполнить вычисления, в которых участвуют данные нескольких строк. Чтобы упростить задачу, эти строки надо развернуть и превратить в столбцы, так чтобы все необходимые значения располагались в одной строке.

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

select deptno, sum(sal) as sal from emp group by deptno

DEPTNO SAL

10 8750

20 10875 30 9400

Надо вычислить разность между заработными платами DEPTNO 20 и DEPTNO 10 и заработными платами DEPTNO 20 и DEPTNO 30.

Решение

С помощью агрегатной функции SUM и выражения CASE транспонируйте результаты. Затем включите свои выражения в список оператора SELECT:

1 select d20 sal - d10 sal as d20 10 diff,

2 d20 sal - d30 sal as d20 30 diff

3 from (

4 select sum(case when deptno=10 then sal end) as d10 sal,

5 sum(case when deptno=20 then sal end) as d20 sal,

6 sum(case when deptno=30 then sal end) as d30 sal

7 from emp

8 ) totals by dept