SQL Server中GROUPING SETS

SQL Server中GROUPING SETS

使用 GROUPING SETS 的 GROUP BY 子句可以生成一个等效于由多个简单 GROUP BY 子句的 UNION ALL 生成的结果集。

一、下面通过两个实例说明其用法

实例一、统计 Staff 表中的性别、部门、薪资、入职年份

1、创建表 Staff

  •  
  • SQL 代码   复制
  • 
    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表填充数据

  •  
  • SQL 代码   复制
  • 
    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 方式统计

  •  
  • SQL 代码   复制
  • 
    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方式统计

  •  
  • SQL 代码   复制
  • 
    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、创建测试数据库及表并插入测试数据

  •  
  • SQL 代码   复制
  • 
    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',标签: