SQL Server中GROUPING SETS

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

		

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'

		

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])

		

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

		

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',
 

  
标签：SQL Server   
 
 



              
上一篇：asp.net判断上传文件格式
                	  
			  
下一篇：linq distinct去重

  
 


  

      您可能感兴趣
				

					

  
SQL Server中的临时表的用法
  
sql server怎么导出数据库（sqlserver复制数据库的方法步骤图文）
  
怎么新建数据库登录名用sql语句（T-sql语句修改SQL Server数据库逻辑名、数据库名、物理名的方法）
  
sqlserver数据库的对象有哪些（详解SQL Server数据库架构和对象、定义数据完整性）
  
sql server日志文件过大怎么办
  
sql server2008自动备份数据（SQL Server数据库定时自动备份）
  
sql server高可用的实现方式（sql server 编译与重编译详解）
  
查看SQL SERVER中某个查询用了多少TempDB空间
  
sqlserver基本知识（sql server学习基础之内存初探）
  
sqlserver备份还原数据（通过Windows批处理命令执行SQL Server数据库备份）
  
sql server中check约束
  
如何在sql server中解密视图（关于SQL Server加密与解密的问题）
  
sql server中的逻辑读、物理读和预读
  
SQL Server日期与字符串之间的转换
  
kettle替换sql语句（sql server定时作业调用Kettle job出错的快速解决方法）
  
Sql Server profiler 分析器的理解
蓝天代表什么（蓝天代表什么生肖）
今天要吃什么（今天要吃什么菜）
营养餐是什么（学校营养餐是什么）
谁说女子不如男 范冰冰演的武则天只是其一，另外两位你认识吗（谁说女子不如男）
杯酒人生---瓦伦丁酒杯和奥丁格啤酒（杯酒人生---瓦伦丁酒杯和奥丁格啤酒）
中秋节买啤酒，预算超过7元试试这8种啤酒，麦香浓郁都是真啤酒（预算超过7元试试这8种啤酒）