Rollup & Cube

最新推荐文章于 2025-12-16 15:49:30 发布

转载最新推荐文章于 2025-12-16 15:49:30 发布 · 485 阅读

文章标签：

#date #table #测试 #insert

MSSQL 专栏收录该内容

85 篇文章

订阅专栏

本文介绍如何使用SQL的WITH ROLLUP和WITH CUBE进行数据聚合与分组操作，通过具体示例展示了如何计算小计和汇总，并提供了一个包含多维度数据的测试案例。

;WITH
TB AS(
SELECT Item = 'Table', Color = 'Blue', Quantity = 124
UNION ALL
SELECT Item = 'Table', Color = 'Red', Quantity = 23
UNION ALL
SELECT Item = 'Chair', Color = 'Blue', Quantity = 101
UNION ALL
SELECT Item = 'Chair', Color = 'Red', Quantity = 34
)
SELECT Item,Color,
Quantity = Sum(Quantity),
Item_flag = GROUPING(Item),--1代表记录来自聚合值;0代表记录来自基表
Color_flag = GROUPING(Color)--同上
FROM TB
GROUP BY Item,Color
WITH ROLLUP --小计和汇总
HAVING GROUPING(Item) = 1 OR GROUPING(Color) = 1

--示例

--测试数据
create table tb([DB-ID] varchar(10),ENTITY varchar(10),DATE varchar(10),[CUST-NO] int,AMOUNT decimal(10,2),TAX decimal(10,2))
insert tb select 'RCHQ','001','2004-11-10',200000,100.00,17.00
union all select 'RCHQ','001','2004-11-10',200000,200.00,34.00
union all select 'RCHQ','001','2004-11-12',200000,150.00,25.50
union all select 'RCHQ','002','2004-11-10',200000,100.00,17.00
union all select 'RCHQ','002','2004-11-10',200000,200.00,34.00
union all select 'RCHQ','002','2004-11-12',200000,150.00,25.50
go

--查询
select [DB-ID],ENTITY,DATE,[CUST-NO],AMOUNT,TAX
from(
	select [DB-ID]=case 
			when grouping([DB-ID])=1 then '合计'
			else [DB-ID] end
		,ENTITY=case 
			when grouping([DB-ID])=1 then ''
			when grouping(ENTITY)=1 then '小计'
			else ENTITY end
		,DATE=case 
			when grouping([DB-ID])=1 then ''
			when grouping(ENTITY)=1 then ''
			when grouping(DATE)=1 then '小计'
			else DATE end
		,[CUST-NO]=case 
			when grouping([DB-ID])=1 then ''
			when grouping(ENTITY)=1 then ''
			when grouping(DATE)=1 then ''
			when grouping([CUST-NO])=1 then '小计'
			else cast([CUST-NO] as varchar) end
		,AMOUNT=sum(AMOUNT),TAX=sum(TAX)
		,s1=grouping([DB-ID]),s2=[DB-ID]
		,s3=grouping(ENTITY),s4=ENTITY
		,s5=grouping(DATE),s6=DATE
		,s7=grouping([CUST-NO]),s8=[CUST-NO]
	from tb
	group by [DB-ID],ENTITY,DATE,[CUST-NO] with rollup
	having grouping([CUST-NO])=1
	union all
	select [DB-ID],ENTITY,DATE,cast([CUST-NO] as varchar),AMOUNT,TAX
		,s1=0,s2=[DB-ID]
		,s3=0,s4=ENTITY
		,s5=0,s6=DATE
		,s7=0,s8=[CUST-NO]
	from tb 
)a order by s1,s2,s3,s4,s5,s6,s7,s8
go

--删除测试
drop table tb

/*--测试结果
DB-ID	ENTITY	DATE		CUST-NO	AMOUNT	TAX
-------- -------- ----------------- --------- -------- ---------
RCHQ	001	2004-11-10	200000	100.00	17.00
RCHQ	001	2004-11-10	200000	200.00	34.00
RCHQ	001	2004-11-10	小计	300.00	51.00
RCHQ	001	2004-11-12	200000	150.00	25.50
RCHQ	001	2004-11-12	小计	150.00	25.50
RCHQ	001	小计			450.00	76.50
RCHQ	002	2004-11-10	200000	100.00	17.00
RCHQ	002	2004-11-10	200000	200.00	34.00
RCHQ	002	2004-11-10	小计	300.00	51.00
RCHQ	002	2004-11-12	200000	150.00	25.50
RCHQ	002	2004-11-12	小计	150.00	25.50
RCHQ	002	小计			450.00	76.50
RCHQ	小计				900.00	153.00
合计					900.00	153.00
--*/