mssql 中group by cube,rollup,grouping示例

mssql 中group by cube,rollup,grouping示例

--建表
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

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

select CustomerID ,sum(salesvalue) as salevalue
from Orders
group by grouping sets (CUBE(customerid))

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

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

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

 

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),())