1.创建表 StaffCREATE TABLE [dbo].[Staff]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL, [Sex] [varchar](50) NULL, [Departme ...
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.换成GROUPING SETS的写法
SET STATISTICS IO ON SET STATISTICS TIME ON GOSELECT (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]),())
海外公司注册、海外银行开户、跨境平台代入驻、VAT、EPR等知识和在线办理：https://www.xlkjsw.com
原标题：SQL Server 之 GROUP BY、GROUPING SETS、ROLLUP、CUBE
关键词：sql
*特别声明：以上内容来自于网络收集，著作权属原作者所有，如有侵权，请联系我们：
admin#shaoqun.com
（#换成@）。
