SQL Server 之 GROUP BY、GROUPING SETS、ROLLUP、CUBE

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

 

贰.为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'

 

GROUP BY 分组查询, 1般和聚合函数合营使用

SELECT  [DEPARTMENT],SEX, COUNT(1)
FROM DBO.[STAFF] 
GROUP BY SEX, [DEPARTMENT]  

该段SQL是用于查询   有些部门下的子女职员和工人数量 其数额结果如下
图片 1

图片 2

图片 3

付出相比较大

 

GROUPING SETS

动用 GROUPING SETS 的 GROUP BY 子句能够生成三个等效于由八个大概 GROUP BY
子句的 UNION ALL 生成的结果集,并且其效能比 GROUP BY 要高,SQL Server
二零零六引入。

一.运用GROUP BY 子句的 UNION ALL 来总结Staff 表中的性别、部门、薪资、入职年份

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

图片 5
图片 6

2.换成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]),())

图片 7

图片 8

图片 9

从上述结果中能够见见,选取UNION ALL 是一再扫描表,并将围观后的查询结果实行结合操作,会扩大IO开支,缩小CPU和内部存款和储蓄器成本。

利用GROUPING
SETS 是三回性读取全体数据,并在内部存款和储蓄器中开始展览联谊操作生成结果,裁减IO开支,对CPU和内部存款和储蓄器消耗扩充。但GROUPING
SETS 在多列分组时,其属性会比group by高。

这边扫描八次是因为本身 GROUP BY GROUPING SETS
(SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]),()) 了四列

 

ROLLUP与CUBE 

ROLLUP与CUBE  按自然的规则发生多样分组,然后按各样分组总括数据

ROLLUP与CUBE 区别:

  CUBE 会对具有的分组字段进行总计,然后合计。

  ROLLUP 依照分组顺序,对第三个字段展开组内总括,最后交给合计。

 

上面看小编查询 

SELECT  
      CASE WHEN (GROUPING(SEX) = 1) THEN '统计-ROLLUP' 
            ELSE ISNULL(SEX, 'UNKNOWN') 
       END AS SEX ,
        COUNT(0)
FROM DBO.[STAFF] 
GROUP BY   SEX   WITH ROLLUP

SELECT  
      CASE WHEN (GROUPING(SEX) = 1) THEN '统计-CUBE' 
            ELSE ISNULL(SEX, 'UNKNOWN') 
       END AS SEX ,
        COUNT(0)
FROM DBO.[STAFF] 
GROUP BY   SEX   WITH CUBE

图片 10

看不出差距,大家再加壹列

SELECT  
      CASE WHEN (GROUPING(SEX) = 1) THEN '统计-ROLLUP' 
            ELSE ISNULL(SEX, 'UNKNOWN') 
       END AS SEX , 
      CASE WHEN (GROUPING([DEPARTMENT]) = 1) THEN '统计-ROLLUP' 
            ELSE ISNULL([DEPARTMENT], 'UNKNOWN') 
       END AS [DEPARTMENT], 
        COUNT(0) 
FROM DBO.[STAFF] 
GROUP BY   SEX,[DEPARTMENT]   WITH ROLLUP

SELECT  
      CASE WHEN (GROUPING(SEX) = 1) THEN '统计-CUBE' 
            ELSE ISNULL(SEX, 'UNKNOWN') 
       END AS SEX ,
      CASE WHEN (GROUPING([DEPARTMENT]) = 1) THEN  '统计-CUBE' 
            ELSE ISNULL([DEPARTMENT], 'UNKNOWN') 
       END AS [DEPARTMENT], 
        COUNT(0) 
FROM DBO.[STAFF] 
GROUP BY   SEX,[DEPARTMENT]  WITH CUBE

图片 11

可以见到 使用 ROLLUP 会先计算分组下的,然后在对GROUP
BY的首先列字段进展总计,最终总计总数,而
CUBE 则是先分组总括,然后总括GRUOP BY 的各类字段,最终进行集中。

 

 http://www.cnblogs.com/woxpp/p/4688715.html 

相关文章