SQL Server 使用 WITH ROLLUP, CUBE, GROUPING 如何汇总数据SQL Server 使

表架构及数据

表架构及数据SQL

CREATE TABLE [dbo].[temp_order](
	[rowid] [int] IDENTITY(1,1) NOT NULL,
	[c_type] [varchar](20) NULL,
	[c_number] [decimal](18, 2) NULL,
	[c_sumprice] [decimal](18, 2) NULL,
	[purchaser] [nvarchar](50) NULL
) ON [PRIMARY]

INSERT [dbo].[temp_order] ([rowid], [c_type], [c_number], [c_sumprice], [purchaser]) VALUES (1, N'A', CAST(1.00 AS Decimal(18, 2)), CAST(100.00 AS Decimal(18, 2)), N'MicroSoft')
INSERT [dbo].[temp_order] ([rowid], [c_type], [c_number], [c_sumprice], [purchaser]) VALUES (2, N'B', CAST(2.00 AS Decimal(18, 2)), CAST(400.00 AS Decimal(18, 2)), N'Google')
INSERT [dbo].[temp_order] ([rowid], [c_type], [c_number], [c_sumprice], [purchaser]) VALUES (3, N'C', CAST(3.00 AS Decimal(18, 2)), CAST(900.00 AS Decimal(18, 2)), N'Apple')
INSERT [dbo].[temp_order] ([rowid], [c_type], [c_number], [c_sumprice], [purchaser]) VALUES (4, N'A', CAST(6.00 AS Decimal(18, 2)), CAST(600.00 AS Decimal(18, 2)), N'Google')
INSERT [dbo].[temp_order] ([rowid], [c_type], [c_number], [c_sumprice], [purchaser]) VALUES (5, N'B', CAST(0.50 AS Decimal(18, 2)), CAST(100.00 AS Decimal(18, 2)), N'Apple')
INSERT [dbo].[temp_order] ([rowid], [c_type], [c_number], [c_sumprice], [purchaser]) VALUES (6, N'C', CAST(2.00 AS Decimal(18, 2)), CAST(600.00 AS Decimal(18, 2)), N'MicroSoft')

初始数据

SELECT * FROM TEMP_ORDER

rowid	c_type	c_number	c_sumprice	purchaser
1	A	1	100	MicroSoft
2	B	2	400	Google
3	C	3	900	Apple
4	A	6	600	Google
5	B	0.5	100	Apple
6	C	2	600	MicroSoft

普通分组

按购买方和货物类别分组，求和货物数量

select purchaser,c_type,sum(c_number) g_num from temp_order
group by purchaser,c_type

purchaser	c_type	g_num
Google	A	6
MicroSoft	A	1
Apple	B	0.5
Google	B	2
Apple	C	3
MicroSoft	C	2

with cube

CUBE：CUBE 生成的结果集显示了所选列中值的所有组合的聚合。分类依据根据group by中的顺序，列显示顺序根据select中的顺序。

顺序1

观察结果发现显示顺序为：先按照最后的c_type分组条件求和，再求总和，最后按照purchaser分组条件求和。

select purchaser,c_type,sum(c_number) g_num from temp_order
group by purchaser,c_type
with cube

purchaser	c_type	g_num
Google	A	6
MicroSoft	A	1
NULL	A	7
Apple	B	0.5
Google	B	2
NULL	B	2.5
Apple	C	3
MicroSoft	C	2
NULL	C	5
NULL	NULL	14.5
Apple	NULL	3.5
Google	NULL	8
MicroSoft	NULL	3

顺序2

与顺序1相比，只更换select中的顺序。观察结果发现g_num列结果并未产生行顺序改变。

select c_type,purchaser,sum(c_number) g_num from temp_order
group by purchaser,c_type
with cube

c_type	purchaser	g_num
A	Google	6
A	MicroSoft	1
A	NULL	7
B	Apple	0.5
B	Google	2
B	NULL	2.5
C	Apple	3
C	MicroSoft	2
C	NULL	5
NULL	NULL	14.5
NULL	Apple	3.5
NULL	Google	8
NULL	MicroSoft	3

顺序3

与顺序1相比，只更换group by中的顺序。观察结果发现g_num列结果产生行顺序改变，得出分类依据根据group by中的顺序。

select purchaser,c_type,sum(c_number) g_num from temp_order
group by c_type,purchaser
with cube

purchaser	c_type	g_num
Apple	B	0.5
Apple	C	3
Apple	NULL	3.5
Google	A	6
Google	B	2
Google	NULL	8
MicroSoft	A	1
MicroSoft	C	2
MicroSoft	NULL	3
NULL	NULL	14.5
NULL	A	7
NULL	B	2.5
NULL	C	5

with rollup

ROLLUP：ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。某一层次结构跟group by的第一个分组条件有关。

顺序1

观察结果发现对第一个purchaser分组条件进行求和，并按所有分组条件求总和。

select purchaser,c_type,sum(c_number) g_num from temp_order
group by purchaser,c_type
with rollup

purchaser	c_type	g_num
Apple	B	0.5
Apple	C	3
Apple	NULL	3.5
Google	A	6
Google	B	2
Google	NULL	8
MicroSoft	A	1
MicroSoft	C	2
MicroSoft	NULL	3
NULL	NULL	14.5

顺序2

与顺序1相比，更换group by中的顺序。观察结果发现对第一个c_type分组条件进行求和，并按所有分组条件求总和。得出某一层次结构跟group by的第一个分组条件有关。

select purchaser,c_type,sum(c_number) g_num from temp_order
group by c_type,purchaser
with rollup

purchaser	c_type	g_num
Google	A	6
MicroSoft	A	1
NULL	A	7
Apple	B	0.5
Google	B	2
NULL	B	2.5
Apple	C	3
MicroSoft	C	2
NULL	C	5
NULL	NULL	14.5

grouping

GROUPING：当行由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 1；当行不由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 0。

顺序1

观察结果发现只有最后一行的purchaser值为ROLLUP 运算符添加，grouping(purchaser)=1

select purchaser,c_type,sum(c_number) g_num,grouping(purchaser) from temp_order
group by purchaser,c_type
with rollup

purchaser	c_type	g_num	(无列名)
Apple	B	0.5	0
Apple	C	3	0
Apple	NULL	3.5	0
Google	A	6	0
Google	B	2	0
Google	NULL	8	0
MicroSoft	A	1	0
MicroSoft	C	2	0
MicroSoft	NULL	3	0
NULL	NULL	14.5	1

顺序2

观察结果发现所有行的c_type值为ROLLUP 运算符添加时，grouping(c_type)=1

select purchaser,c_type,sum(c_number) g_num,grouping(c_type) from temp_order
group by purchaser,c_type
with rollup

purchaser	c_type	g_num	(无列名)
Apple	B	0.5	0
Apple	C	3	0
Apple	NULL	3.5	1
Google	A	6	0
Google	B	2	0
Google	NULL	8	1
MicroSoft	A	1	0
MicroSoft	C	2	0
MicroSoft	NULL	3	1
NULL	NULL	14.5	1

实战：实现显示小计和总计

按购买方和货物类别分组，求和货物数量并显示小计和总计。

select purchaser,
	case when grouping(purchaser)=1 then '总计'
	else
		case when grouping(c_type)=1 then '小计'
		else c_type
		end
	end 'c_type'
,sum(c_number) g_num from temp_order
group by purchaser,c_type
with rollup

purchaser	c_type	g_num
Apple	B	0.5
Apple	C	3
Apple	小计	3.5
Google	A	6
Google	B	2
Google	小计	8
MicroSoft	A	1
MicroSoft	C	2
MicroSoft	小计	3
NULL	总计	14.5