Если вам когда-либо понадобилось показать подробные данные отдельных транзакций, сохраняя при этом накопительные итоги, промежуточные итоги и столбцы общего итога одновременно, то этот статья для вас. В этой статье мы рассмотрим различные методы расчета и суммирования информации по нескольким строкам без использования курсора. Для этого мы будем использовать основные операторы SELECT и SQL-клаузы и функции, такие как SUM и CASE.

Пример данных

Прежде чем мы приступим к примерам, давайте создадим набор тестовых данных, которые мы будем использовать. Мы создадим таблицу “Заказы” со следующей структурой:

create table Orders (
    OrderID int identity,
    OrderAmt Decimal(8,2),
    OrderDate SmallDatetime
)

Мы заполним эту таблицу следующим набором записей:

Накопительный итог для каждой записи

Начнем с простого примера, который вычисляет накопительный итог OrderAmt и отображает его вместе с каждой записью в таблице Заказы. Мы можем сделать это, используя коррелированный подзапрос в операторе SELECT:

SELECT OrderId, OrderDate, O.OrderAmt,
    (SELECT SUM(OrderAmt) FROM Orders WHERE OrderID <= O.OrderID) AS 'Накопительный итог'
FROM Orders O

Выполнение этого запроса для нашей таблицы Заказы даст нам следующие результаты:

Как видите, столбец “Накопительный итог” отображает накопительный итог вместе с другой информацией о столбцах, связанных с каждой записью таблицы Заказы. Накопительный итог рассчитывается путем суммирования OrderAmt для всех заказов, где OrderID меньше или равен OrderID текущей отображаемой записи.

Накопительный итог для каждой даты заказа

В этом примере мы будем вычислять накопительный итог для каждой записи, но только если дата заказа для записей находится в один и тот же день. Когда дата заказа относится к другому дню, начинается новый накопительный итог и накапливается для нового дня. Мы можем сделать это, изменяя наш предыдущий запрос:

SELECT O.OrderId, CONVERT(char(10), O.OrderDate, 101) AS 'Дата заказа', O.OrderAmt,
    (SELECT SUM(OrderAmt) FROM Orders WHERE OrderID <= O.OrderID AND CONVERT(char(10), OrderDate, 101) = CONVERT(char(10), O.OrderDate, 101)) AS 'Накопительный итог'
FROM Orders O
ORDER BY OrderID

Результаты, возвращаемые этим запросом с использованием нашей таблицы Заказы, будут следующими:

Обратите внимание, что “Накопительный итог” начинается с значением 10.50, а затем становится 22.00 и, наконец, 23.25 для OrderID 3, поскольку все эти записи имеют одну и ту же дату заказа (10/11/2003). Но когда отображается OrderID 4, накопительный итог сбрасывается, и начинается новый накопительный итог. Это происходит потому, что у OrderID 4 другая дата для его даты заказа, чем у OrderID 1, 2 и 3. Расчет этого накопительного итога для каждой уникальной даты достигается с помощью коррелированного подзапроса с дополнительным условием WHERE, которое гарантирует, что даты заказов на разных записях являются одним и тем же днем.

Накопительные итоги с промежуточными итогами и общим итогом

В этом примере мы будем вычислять единый промежуточный итог для всех заказов, созданных в один день, и общий итог для всех заказов. Мы будем использовать оператор CASE в операторе SELECT для достижения этой цели:

SELECT O.OrderID, CONVERT(char(10), O.OrderDate, 101) AS 'Дата заказа', O.OrderAmt,
    CASE
        WHEN OrderID = (SELECT TOP 1 OrderId FROM Orders WHERE CONVERT(char(10), OrderDate, 101) = CONVERT(char(10), O.OrderDate, 101) ORDER BY OrderID DESC)
        THEN (SELECT CAST(SUM(OrderAmt) AS char(10)) FROM Orders WHERE OrderID <= O.OrderID AND CONVERT(char(10), OrderDate, 101) = CONVERT(char(10), O.OrderDate, 101))
        ELSE ' '
    END AS 'Промежуточный итог',
    CASE
        WHEN OrderID = (SELECT TOP 1 OrderId FROM Orders ORDER BY OrderDate DESC)
        THEN (SELECT CAST(SUM(OrderAmt) AS char(10)) FROM Orders)
        ELSE ' '
    END AS 'Общий итог'
FROM Orders O
ORDER BY OrderID

Результаты, возвращаемые этим оператором SELECT, будут выглядеть следующим образом:

В этом примере первый оператор CASE управляет печатью столбца “Промежуточный итог”. Промежуточный итог печатается только на последнем заказе дня, что определяется с помощью коррелированного подзапроса. Второй оператор CASE печатает “Общий итог”, который печатается только вместе с самым последним заказом. Каждый из этих операторов CASE использует клаузу TOP для определения того, какой OrderID является правильным для печати “Общего итога”.

Заключение

Эти примеры демонстрируют различные методы, которые можно использовать для расчета накопительных итогов, промежуточных итогов и общего итога в SQL Server. Вам не нужно использовать курсор для расчета этих различных итогов. С помощью творческого использования коррелированных подзапросов и других операторов SELECT, таких как CASE, вы легко можете создать все эти различные итоги. В следующий раз, когда вам понадобится рассчитать итоги, рассмотрите возможность использования одного из этих решений, не основанных на курсоре.