30 JAMES

30 TURNER

30 WARD

03-DEC-1981 08-SEP-1981 22-FEB-1981

1500 1250

1250

2850

Заключительный шаг - найти разность между значениями SAL и NEXT SAL, а в случае необходимости возвратить N/A . Для этого используем функцию COALESCE. Поскольку результатом вычитания является число или NULL, в функции COALESCE необходимо выполнить приведение результата вычитания к строковому типу:

select deptno,ename,hiredate,sal,

coalesce(cast(sal-next sal as char(10)),N/A) as diff from ( select e.deptno,

e.ename,

e.hiredate,

e.sal,

(select min(sal) from emp d where d.deptno=e.deptno and d.hiredate =

(select min(hiredate) from emp d where e.deptno=d.deptno and d.hiredate > e.hiredate)) as next sal

from emp e ) x

order by 1

DEPTNO ENAME

HIREDATE

SAL DIFF

Применение в данном решении MIN(SAL) является примером того, как можно непреднамеренно ввести в запрос бизнес-логику при решении чисто технической задачи. Если данной дате соответствуют несколько заработных плат, какую следует выбрать? Наименьшую? Наибольшую? Среднюю? В своем примере я решил брать наименьшую. В реальности я бы предоставил возможность бизнес-клиенту, запросившему отчет, принимать это решение.

DEPTNO ENAME

SAL HIREDATE

NEXT SAL

Следующий шаг - для каждого служащего найти разность между его заработной платой и заработной платой служащего, принятого на работу в этот отдел сразу после него:

select deptno,ename,sal,hiredate, sal-next sal diff from (

select deptno,ename,sal,hiredate,

lead(sal)over(partition by deptno order by hiredate) next sal from emp

DEPTNO ENAME

10 CLARK

10 KING

10 MILLER

20 SMITH

20 JONES

20 FORD

20 SCOTT

20 ADAMS

30 ALLEN

30 WARD

30 BLAKE

30 TURNER

SAL HIREDATE

DIFF

2450 5000 1300 800 2975 3000 3000 1100 1600 1250 2850 1500

09-JUN-1981 17-NOV-1981 23-JAN-1982 17-DEC-1980 02-APR-1981 03-DEC-1981 09-DEC-1982 12-JAN-1983 20-FEB-1981 22-FEB-1981 01-MAY-1981 08-SEP-1981

-2550 3700

-2175 -25

1900

350 -1600 1350

Первый шаг - с помощью оконной функции LEAD OVER найти для каждого служащего заработную плату следующего служащего в его отделе. В столбце NEXT SAL для служащего любого отдела, принятого на работу последним, располагается значение NULL:

select deptno,ename,sal,hiredate,

lead(sal)over(partition by deptno order by hiredate) next sal from emp

30 MARTIN

30 JAMES

1250 28-SEP-1981 950 03-DEC-1981

Следующий шаг - использовать функцию NVL, чтобы возвратить значение N/A , если DIFF является NULL. Чтобы возвратить N/A , необходимо привести значение DIFF к строковому типу, в противном случае функция NVL даст сбой:

select deptno,ename,sal,hiredate,

nvl(to char(sal-next sal),N/A) diff from (

select deptno,ename,sal,hiredate,

lead(sal)over(partition by deptno order by hiredate) next sal from emp

DEPTNO ENAME

SAL HIREDATE

DIFF

10 10 10 20 20 20 20 20 30 30 30 30 30 30

CLARK

KING

MILLER

SMITH

JONES

FORD

SCOTT

ADAMS

ALLEN

WARD

BLAKE

TURNER

MARTIN

JAMES

2450 5000 1300

800 2975 3000 3000 1100 1600 1250 2850 1500 1250

09-JUN-1981 17-NOV-1981 23-JAN-1982 17-DEC-1980 02-APR-1981 03-DEC-1981 09-DEC-1982 12-JAN-1983 20-FEB-1981 22-FEB-1981 01-MAY-1981 08-SEP-1981 28-SEP-1981 03-DEC-1981

-2550

3700

-2175

1900

-1600

1350

Последний шаг - с помощью функции LPAD форматировать значения для столбца DIFF. Это необходимо, потому что по умолчанию числа выравниваются по правому краю, а строки - по левому. Используя LPAD, мы выравниваем все значения столбца по правому краю:

select deptno,ename,sal,hiredate,

lpad(nvl(to char(sal-next sal),N/A),10) diff from (

select deptno,ename,sal,hiredate,

lead(sal)over(partition by deptno order by hiredate) next sal from emp

DEPTNO ENAME

SAL HIREDATE

DIFF

10 CLARK

10 KING

10 MILLER

20 SMITH

20 JONES

2450 09-JUN-1981 -2550

5000 17-NOV-1981 3700

1300 23-JAN-1982 N/A

800 17-DEC-1980 -2175

2975 02-APR-1981 -25