Добавление итогов

Дата добавления: 2015-07-09; просмотров: 560; Нарушение авторских прав

Еще раз рассмотрим таблицу bills созданную в пункте об агрегатных функциях. Предположим мы хотим вывести все суммы, а в конце выборки добавить итоговую сумму. Наиболее универсальным способом является объединение двух запросов.

-- в MySQL между cast и ( не должно быть пробеловselect cast(t.d as char(12)) as d, t.summ from bills t union select 'ИТОГ', sum(t.summ) from bills t;

Для решения подобных задач в стандарте введена конструкция ROLLUP генерирующая дополнительную строку. Если в определении столбца агрегатная функция не используется, то соответствующее поле в этой строке заполняется значением null. В противном случае заполняется значением выражения столбца, причем агрегатная функция выполняется ко всем записям основной выборки.

-- для Oracle, столбцы группировки перешли в ROLLUPselect coalesce(cast(t.d as char(12)),'ИТОГ') as d, sum(t.summ) as summ from bills t group by ROLLUP(t.d, t.summ) -- для MySQLselect coalesce(cast(t.d as char(12)),'ИТОГ') as d, sum(t.summ) as summ from bills t group by t.d, t.summ WITH ROLLUP;

А теперь предположим мы хотим вывести все суммы с итогами по каждой группе и в конце выборки общий итог. Ниже приведен пример с использованием объединений. Чтобы общий итог был точно в конце выборки, задаем в поле d максимальню дату. В Oracle и Postgre можно оставить значение null.

select t.d1, t.summ from ( select cast(t.d as char(12)) as d1, t.d, t.summ from bills t union select -- выборка итогов по группам 'итого' as d1, t.d, sum(t.summ) as summ from bills t group by t.d union select -- выборка общего итога 'ИТОГО' as d1, DATE '9999-01-01' as d, sum(t.summ) as summ from bills t ) torder by d,summ

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

select * from ( select t.d, t.summ, sum(t.summ) as itog from bills t -- для MySQL: group by t.d, t.summ WITH CUBE group by cube(t.d, t.summ) -- для Oracle order by d ) t where -- убираем не нужные под итоги not(t.summ=itog and d is null) or (d is null and summ is null)

<== предыдущая лекция	\|	следующая лекция ==>
Операции над выборками	\|	Нумерация записей