SQL Server中GROUPING SETS
SQL Server中GROUPING SETS使用 GROUPING SETS 的 GROUP BY 子句可以生成一个等效于由多个简单 GROUP BY 子句的 UNION ALL 生成的结果集。
一、下面通过两个实例说明其用法
实例一、统计 Staff 表中的性别、部门、薪资、入职年份
1、创建表 Staff
CREATE TABLE [dbo].[Staff](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Sex] [varchar](50) NULL,
[Department] [varchar](50) NULL,
[Money] [int] NULL,
[CreateDate] [datetime] NULL
) ON [PRIMARY]
GO
2、为Staff表填充数据
INSERT INTO [dbo].[Staff]([Name],[Sex],[Department],[Money],[CreateDate])
SELECT 'Name1','男','技术部',3000,'2011-11-12'
UNION ALL
SELECT 'Name2','男','工程部',4000,'2013-11-12'
UNION ALL
SELECT 'Name3','女','工程部',3000,'2013-11-12'
UNION ALL
SELECT 'Name4','女','技术部',5000,'2012-11-12'
UNION ALL
SELECT 'Name5','女','技术部',6000,'2011-11-12'
UNION ALL
SELECT 'Name6','女','技术部',4000,'2013-11-12'
UNION ALL
SELECT 'Name7','女','技术部',5000,'2012-11-12'
UNION ALL
SELECT 'Name8','男','工程部',3000,'2012-11-12'
UNION ALL
SELECT 'Name9','男','工程部',6000,'2011-11-12'
UNION ALL
SELECT 'Name10','男','工程部',3000,'2011-11-12'
UNION ALL
SELECT 'Name11','男','技术部',3000,'2011-11-12'
3、使用GROUP BY 子句的 UNION ALL 方式统计
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT N'总人数' ,'',COUNT(0) FROM [DBO].[STAFF]
UNION ALL
SELECT N'按性别划分', SEX,COUNT(0) FROM [DBO].[STAFF] GROUP BY SEX
UNION ALL
SELECT N'按部门统计',[DEPARTMENT],COUNT(0) FROM [DBO].[STAFF] GROUP BY [DEPARTMENT]
UNION ALL
SELECT N'按薪资统计',CONVERT(VARCHAR(10),[MONEY]),COUNT(0) FROM [DBO].[STAFF] GROUP BY [MONEY]
UNION ALL
SELECT N'按入职年份',CONVERT(VARCHAR(10),YEAR([CREATEDATE])),COUNT(0) FROM [DBO].[STAFF] GROUP BY YEAR([CREATEDATE])
效果图
执行计划
4、使用GROUPING SETS方式统计
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT (CASE
WHEN GROUPING_ID(SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]))=15 THEN N'总人数'
WHEN GROUPING_ID(SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]))=7 THEN N'按性别划分'
WHEN GROUPING_ID(SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]))=11 THEN N'按部门统计'
WHEN GROUPING_ID(SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]))=13 THEN N'按薪资统计'
WHEN GROUPING_ID(SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]))=14 THEN N'按入职年份'
END
),
(CASE
WHEN GROUPING_ID(SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]))=15 THEN ''
WHEN GROUPING_ID(SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]))=7 THEN SEX
WHEN GROUPING_ID(SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]))=11 THEN [DEPARTMENT]
WHEN GROUPING_ID(SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]))=13 THEN CONVERT(VARCHAR(10),[MONEY])
WHEN GROUPING_ID(SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]))=14 THEN CONVERT(VARCHAR(10),YEAR([CREATEDATE]))
END
)
,
COUNT(1)
FROM DBO.[STAFF]
GROUP BY GROUPING SETS (SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]),())
效果图
执行计划
实例二、统计产品销量
计算出每天的销量,总销量,每个销售员的总销量,每个产品的总销量,每个品牌的总销量,及每个销售员按品牌的产品销量
1、创建测试数据库及表并插入测试数据
use master
CREATE DATABASE db_sales
go
use db_sales
go
CREATE TABLE [dbo].[tb_sale](
[id] [int] IDENTITY(1,1) NOT NULL,
[server] [nvarchar](50) NULL,
[pname] [nvarchar](50) NULL,
[pinpai] [nvarchar](50) NULL,
[dates] [smalldatetime] NULL,
[cnt] [int] NULL
) ON [PRIMARY]
go
INSERT INTO [db_ControlManager_ft].[dbo].[test]([server],[pname],[pinpai],[dates],[cnt])
VALUES('A','computer','hp','2012-01-01',1),
('A','computer','hp','2012-01-02',3),
('A','computer','hp','2012-01-03',5),
('A','computer','hp','2012-01-04',1),
('A','computer','hp','2012-01-05',3),
('A','computer','hp','2012-01-06',5),
('A','computer','dell','2012-01-01',2),
('A','computer','dell','2012-01-02',4),
('A','computer','dell','2012-01-03',6),
('A','computer','dell','2012-01-04',7),
('A','computer','dell','2012-01-05',2),
('A','computer','dell','2012-01-06',4),
('B','computer','hp','2012-01-01',3),
('B','computer','hp','2012-01-02',3),
('B','computer','hp','2012-01-03',3),
('B',