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

 

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'

 

GROUP BY 分组查询, 一般与聚合函数配合下

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
2008引入。

1.以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 

相关文章