2450 5000

1300

2975

3000

1100 3000

1600 1250

1250 2850

1500 950

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

select sum(case when deptno=10 then sal end) as d10 sal, sum(case when deptno=20 then sal end) as d20 sal, sum(case when deptno=30 then sal end) as d30 sal from emp

D10 SAL D20 SAL D30 SAL

8750 10875 9400

Заключительный шаг - просто оформить приведенный выше SQL как вложенный запрос и найти разности.

Создание блоков данных фиксированного размера Задача

Требуется организовать данные в одинаковые по размеру блоки с предопределенным количеством элементов в каждом блоке. Общее число блоков может быть неизвестно, но каждый из них должен гарантированно содержать пять элементов. Например, необходимо организовать служащих из таблицы EMP в группы по пять на основании значения EMPNO, как показано ниже:

GRP EMPNO ENAME

1 7369 SMITH 1 7499 ALLEN

select case when deptno=10 then sal end as d10 sal,

case when deptno=20 then sal end as d20 sal,

case when deptno=30 then sal end as d30 sal from emp

D10 SAL D20 SAL D30 SAL 800

Решение

Решение данной задачи существенно упрощается, если СУБД обеспечивает функции для ранжирования строк. Когда строки ранжированы, для создания блоков по пять строк остается только выполнить деление и определить верхнюю границу для частного.

DB2, Oracle и SQL Server

Используйте ранжирующую функцию ROW NUMBER OVER, чтобы ранжировать служащих по EMPNO. Затем, чтобы создать группы, разделите полученные ранги на 5 (для SQL Server будет использоваться не функция CEIL, а функция CEILING):

1 select ceil(row number()over(order by empno)/5.0) grp,

2 empno,

3 ename

4 from emp

PostgreSQL и MySQL

С помощью скалярного подзапроса ранжируйте строки по EMPNO. Затем разделите полученные ранги на 5, чтобы создать группы:

1 select ceil(rnk/5.0) as grp,

2 empno, ename

3 from (

4 select e.empno, e.ename,

5 (select count(*) from emp d

6 where e.empno < d.empno)+1 as rnk

7 from emp e

8 ) x

9 order by grp

Обсуждение

DB2, Oracle и SQL Server

Ранжирующая функция ROW NUMBER OVER присваивает ранги или порядковые номера строкам, сортированным по столбцу EMPNO:

Следующий шаг - применить функцию CEIL (или CEILING) после деления результата ROW NUMBER OVER на пять. Деление на пять логически организует строки в группы по пять, т. е. пять значений, которые меньше или равны 1; пять значений, которые больше 1, но меньше или равны 2; оставшаяся группа (состоящая из четырех последних строк, поскольку 14, количество строк в таблице EMP, не кратно 5) соответствует значениям, которые больше 2, но меньше или равны 3.

Функция CEIL возвращает наименьшее целое число, которое больше, чем переданное в нее значение; это обеспечит создание групп целых чисел. Результаты деления и применения CEIL представлены ниже. Можно проследить порядок операций слева направо, от RN до DIVISION иGRP:

select row number()over(order by empno) rn,

row number()over(order by empno)/5.0 division, ceil(row number()over(order by empno)/5.0) grp, empno, ename from emp

select row number()over(order by empno) rn,