loading..
Русский    English
06:57
листать

Получение итоговых данных с помощью оператора ROLLUP

Посчитаем сумму прихода на каждый из пунктов по таблице Income. Это несложно сделать при помощи запроса

Консоль
Выполнить
  1. SELECT point, SUM(inc) Qty
  2. FROM Income GROUP BY point;

Пусть наряду с этим нам требуется вывести сумму по всем пунктам, т.е. результат должен выглядеть так:

point    Qty
1    66500.00
2    13000.00
3    3100.00
ALL    82600.00

Для решения подобной задачи в операторе SELECT имеется спецификация ROLLUP. С её помощью достичь требуемого результата не составляет труда:

Консоль
Выполнить
  1. SELECT CASE WHEN point IS NULL THEN 'ALL' ELSE CAST(point AS varchar) END point,
  2. SUM(inc) Qty
  3. FROM Income GROUP BY point WITH ROLLUP;

Поскольку значения столбца должны быть одного типа, номер пункта приёма приводится к символьному типу.

Последний запрос можно переписать в иной (стандартной) синтаксической форме:

Консоль
Выполнить
  1. SELECT CASE WHEN point IS NULL THEN 'ALL' ELSE CAST(point AS varchar) END point,
  2. SUM(inc) Qty
  3. FROM Income GROUP BY ROLLUP(point);

Если СУБД не поддерживает конструкцию ROLLUP, можно использовать либо UNION, либо внешнее соединение (FULL JOIN), что позволяет объединить два запроса в один.

Ниже приводятся эти решения.

UNION

Консоль
Выполнить
  1. SELECT CAST(point AS varchar) point, SUM(inc) Qty
  2. FROM Income GROUP BY point
  3. UNION ALL
  4. SELECT 'ALL', SUM(inc)
  5. FROM Income;

FULL JOIN

Консоль
Выполнить
  1. SELECT coalesce(X.point,Y.point) point, coalesce(X.Qty,Y.Qty) Qty FROM
  2. (SELECT CAST(point AS varchar) point, SUM(inc) Qty
  3. FROM Income GROUP BY point) X
  4. FULL JOIN
  5. (SELECT 'ALL' point, SUM(inc) Qty
  6. FROM Income) Y ON 1 = 2;

В последнем решении следует обратить внимание на то, что соединение выполняется по заведомо ложному предикату, т.к. нам нужны строки из обеих таблиц, которые бы не конкатенировались друг с другом.

Рекомендуемые упражнения: 120

Bookmark and Share
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker MAX Больше тэгов
Учебник обновлялся
вчера
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100