Промежуточные итоги и SQL

У меня есть таблица SQL с данными о продажах (например, приведенная ниже), как я могу сгенерировать набор результатов, который включает промежуточные итоги и в идеале сортирует результаты в порядке убывания самого высокооплачиваемого клиента?

Итак, учитывая следующую таблицу:

CUS_ID  | PRODUCT | AMOUNT
12      | A       |  2.50
12      | B       |  5.80
24      | A       |  10.00
24      | B       |  30.00

Я бы получил следующий результат:

CUS_ID  | Product | AMOUNT
24      | A       |  10.00
24      | B       |  30.00
24      | Total   |  30.00
12      | A       |  2.50
12      | B       |  5.00
12      | Total   |  7.50

Пока я могу прийти со следующим запросом:

  SELECT cus_id, product, amount FROM Sales
UNION ALL
  (SELECT cus_id, 'ZZZZ' AS product, SUM(amount) FROM Sales GROUP BY cus_id)
ORDER BY cus_id, product

Однако запрос использует «ZZZZ» вместо «Всего» (что можно исправить с помощью поиска и замены впоследствии), но он не сортируется в порядке суммы.

EDIT: пожалуйста, не стесняйтесь публиковать ответы, которые не относятся к сортировке. Некоторые ответы оказались для меня очень полезными.


sql sql-server group-by union
person notnoop    schedule 23.12.2010    source источник
comment
Но почему вы включаете «Всего» в тот же набор результатов, что и данные о «продажах»? База данных не является инструментом представления данных, и наличие продукта под названием «Итого» не имеет никакого смысла. Почему бы не создать два отдельных запроса — один для данных о продажах, а другой для итогов?   -  person Kirk Broadhurst    schedule 23.12.2010
comment
@Kirk, клиент ожидает этот формат в одном отчете о продажах (за исключением многих показателей, помимо суммы). Хотя спецификация формата кажется жестким требованием, я хотел проделать как можно больше работы в SQL, прежде чем развертывать свой собственный код.   -  person notnoop    schedule 23.12.2010

Ответы (2)


arrow_upward
5
arrow_downward

Посмотрите на что-то вроде

DECLARE @Sales TABLE(
        CUS_ID  INT,
        PRODUCT VARCHAR(20),
        AMOUNT FLOAT
)
INSERT INTO @Sales SELECT 12,'A', 2.50 
INSERT INTO @Sales SELECT 12,'B', 5.80 
INSERT INTO @Sales SELECT 24,'A', 10.00 
INSERT INTO @Sales SELECT 24,'B', 30.00

;WITH Vals AS (
        SELECT  cus_id, 
                product, 
                amount,
                1 DisplayOrder,
                SUM(amount) OVER(PARTITION BY cus_id) OrderTotal
        FROM    @Sales 
        UNION ALL   
        SELECT  cus_id, 
                'Total' AS product, 
                SUM(amount),
                2 DisplayOrder,
                SUM(amount)
        FROM    @Sales 
        GROUP BY cus_id
)
SELECT  cus_id,
        product,
        amount
FROM    Vals
ORDER BY  OrderTotal DESC,cus_id,DisplayOrder, product
person Adriaan Stander    schedule 23.12.2010

arrow_upward
4
arrow_downward

Поскольку вы счастливы, что у вас нет сортировки, вот быстрый ответ, прежде чем я вернусь к работе. На сервере Sql есть фантастическая функция отчетности - With Rollup. Он автоматически добавляет промежуточные и итоговые строки к запросу; на основе групп, которые вы используете.

В качестве очень быстрого примера использования определения таблицы @astander

   DECLARE @Sales TABLE(
            CUS_ID  INT,
            PRODUCT VARCHAR(20),
            AMOUNT FLOAT
    )
    INSERT INTO @Sales SELECT 12,'A', 2.50 
    INSERT INTO @Sales SELECT 12,'B', 5.80 
    INSERT INTO @Sales SELECT 24,'A', 10.00 
    INSERT INTO @Sales SELECT 24,'B', 30.00

Select Cus_ID, Product, SUM(Amount)
From @Sales
group by cus_id, product with rollup

Это возвращает

Cus_ID      Product              
----------- -------------------- ----------------------
12          A                    2.5
12          B                    5.8
12          NULL                 8.3
24          A                    10
24          B                    30
24          NULL                 40
NULL        NULL                 48.3

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

DECLARE @Sales TABLE(
        CUS_ID  INT,
        PRODUCT VARCHAR(20),
        AMOUNT FLOAT
)
INSERT INTO @Sales SELECT 12,'A', 2.50 
INSERT INTO @Sales SELECT 12,'B', 5.80 
INSERT INTO @Sales SELECT 24,'A', 10.00 
INSERT INTO @Sales SELECT 24,'B', 30.00

select 
    case 
        when GROUPING(product) = 1 and GROUPING(cus_id) = 0 then 'Sub Total - Customer -' + cast(Cus_ID as varchar(10)) 
        when GROUPING(product) = 1 and GROUPING(cus_id) = 1 then 'Total'
        else cast(Cus_ID as varchar(10)) end as Cus_ID,
        PRODUCT, 
    sum(Amount) as Total_Amount 
From @sales 
group by cus_id, product with rollup

Возвращение

Cus_ID                           PRODUCT              Total_Amount
-------------------------------- -------------------- ----------------------
12                               A                    2.5
12                               B                    5.8
Sub Total - Customer -12         NULL                 8.3
24                               A                    10
24                               B                    30
Sub Total - Customer -24         NULL                 40
Total                            NULL                 48.3

Небольшая настройка заставит его выполнить сортировку, которую вы ищете.

person u07ch    schedule 23.12.2010