小计和SQL

一尘不染

小计和SQL

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”而不是“总计”（此后可以通过查找和替换来固定），但是查询没有按金额顺序排序。

编辑：请随时发布不涉及排序的答案。实际上，一些答案对我很有帮助。

阅读 160

2021-03-10

共1个答案

一尘不染

看看像

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

2021-03-10