SQL中 Group by Grouping Sets 分组的用法

1. 用法

将Grouping Sets 运算符添加到Group by 子句中，使用Grouping Set 可以在一个查询中指定数据的多个分组，其结果与针对指定的组执行union all 运算等效

2. 语法

SELECT column1, column2, ..., aggregate_function(column)  
FROM table_name  
GROUP BY GROUPING SETS ((column1, column2, ...), (column1), (column2), ...);

注意事项：

• 在Group by 子句中，如果不是Grouping sets 列表的一部分，不能使用Grouping sets 进行分组。
• 不能在Grouping sets 内部使用Grouping sets

3. 实际应用

假设现在有一个订单表 Orders ,包含订单号、顾客信息、产品和订单金额四个字段，如下图所示

建表语句如下：

--建表
if object_id('Orders','u') is not null drop table Orders
go
create table Orders (
    OrderID INT
    ,CustomerID  INT
    ,CustomerName varchar(20)
    ,ProductName varchar(20)
    ,SalesValue   int
)
go
insert into Orders
values
(1,1, 'Lily','打印机',908)
,(2,1,'Lily','碎纸机',560)
,(3,2,'Coco','传真机',380)
,(4,2,'Coco','打印机',710)
,(5,2,'Coco','办公桌',420)
,(6,3,'Joe','办公椅',120)
,(7,4,'Emma','碎纸机',570)
,(8,1,'Lily','碎纸机',260)
,(9,3,'Joe','办公椅',100)
go

3.1 求总和与小计

使用 Grouping Sets 可以同时计算总和和小计。
如果想要计算每个顾客的订单总金额以及每个顾客、产品的小计金额。可以使用以下查询：

select CustomerID  ,isnull(productname,'产品合计') as ProductName ,sum(salesvalue) as salevalue
from Orders
group by grouping sets ((customerid,productname),(customerid))

查询结果如下图所示：

3.2 按多个维度分组

3.3 标记小计和总计

在查询结果中，可以使用 CASE 语句将小计和总计的结果进行标记。如果想要计算每个顾客的订单总金额以及每个顾客、产品的小计金额；除此之外还需要计算所有订单的销售额总计，可以使用以下查询：

select CustomerID
     ,case when grouping (customerid) = 1 then '总计'
         else isnull(ProductName,'产品合计') end  as ProductName
     ,sum(salesvalue) as salevalue
from Orders
group by grouping sets ((customerid,productname),(customerid),())

查询结果如下图所示：