Далее используем расширение ROLLUP оператора GROUP BY и создаем подсуммы для каждого DEPTNO и по всем служащим (которые находятся в чьем-либо подчинении):

select deptno,mgr,sum(sal) sal

from emp where mgr is not null group by deptno,mgr with rollup

Когда подсуммы найдены, необходим способ определить, какое из значений является подсуммой (создано ROLLUP), а какое - результатом выполнения обычного GROUP BY. С помощью функции GROUPING создайте битовые карты, помогающие отличить подсуммы от обычных агрегатов:

select deptno,mgr,sum(sal) sal,

cast(grouping(deptno) as char(1))+ cast(grouping(mgr) as char(1)) flag from emp where mgr is not null group by deptno,mgr with rollup

TOTAL

24025

Строки со значением 00 в поле FLAG являются результатом обычной агрегации. Строки со значением 01 в поле FLAG - результаты выполнения ROLLUP, осуществляющего агрегацию SAL по DEPTNO (поскольку DEPTNO указан в списке ROLLUP первым; если изменить порядок, например GROUP BY MGR, DEPTNO WITH ROLLUP , результаты будут совершенно иными). Строка со значением 11 в поле FLAG - результат выполнения ROLLUP, суммирующего SAL по всем строкам.

Теперь у нас есть все необходимое для создания красивого отчета с помощью простых выражений CASE. Цель - сформировать отчет, представляющий заработные платы подчиненных всех руководителей по отделам. Если в каком-то отделе данному руководителю не подчиняется ни один служащий, должен быть возвращен нуль; в противном случае требуется возвратить сумму всех заработных плат подчиненных этого руководителя в данном отделе. Кроме того, должен быть добавлен столбец TOTAL, представляющий сумму всех заработных плат. Решение, удовлетворяющее всем этим требованиям, показано ниже:

select mgr,

sum(case deptno when 10 then sal else 0 end) dept10, sum(case deptno when 20 then sal else 0 end) dept20, sum(case deptno when 30 then sal else 0 end) dept30, sum(case flag when 11 then sal else null end) total from (

select deptno,mgr,sum(sal) sal,

cast(grouping(deptno) as char(1))+ cast(grouping(mgr) as char(1)) flag

Иерархические запросы

В данной главе приведены рецепты для отображения иерархических отношений, которые могут присутствовать в данных. Обычно хранить иерархические данные легче, чем извлекать и отображать их (в иерархическом виде). Именно в таких задачах особенно сильно ощущается недостаток гибкости SQL и его нерекурсивная природа. При работе с иерархическими запросами абсолютно необходимо использовать предоставляемые для этого вашей СУБД возможности; в противном случае запросы будут неэффективными либо придется создавать замысловатые модели данных. Скорее всего, рекурсивный оператор WITH будет добавлен в следующие версии PostgreSQL, поэтому пользователям PostgreSQL следует обращать внимание на решения для DB2.

В данной главе будут предложены рецепты, которые помогут отобразить иерархическую структуру данных, используя функции, предоставляемые СУБД. Рассмотрим сначала таблицу EMP и взаимосвязь между столбцами EMPNO и MGR:

select empno,mgr