我们有的时候要对数据进要做小计合计等工作,普通的方式,我们可以用聚合函数,然后配合UNION等函数处理,SQL SERVER其实提供了CUBE来实现这个功能。
以下是介绍:
CUBE 运算符生成的结果集是多维数据集。多维数据集是事实数据(即记录个别事件的数据)的扩展。扩展是基于用户要分析的列建立的。这些列称为维度。多维数据集是结果集,其中包含各维度的所有可能组合的交叉表格。 CUBE 运算符在 SELECT 语句的 GROUP BY 子句中指定。该语句的选择列表包含维度列和聚合函数表达式。GROUP BY 指定了维度列和关键字 WITH CUBE。结果集包含维度列中各值的所有可能组合,以及与这些维度值组合相匹配的基础行中的聚合值。
看个例子,测试数据:
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([Item] nvarchar(22),[Color] nvarchar(22),[Quantity] int)
Insert #T
select N'桌子',N'白色',1 union all
select N'桌子',N'红色',2 union all
select N'椅子',N'白色',3 union all
select N'椅子',N'红色',4
Go
--测试数据结束
如果我们想统计各种颜色的买了多少,并且最后合计一共买了多少,用比较笨的方法可以这样实现:
Select Color,SUM(Quantity) AS Quantity from #T GROUP BY Color
UNION
SELECT '合计',SUM(Quantity) FROM #T
结果:
我们可以看到顺序还有问题,还得要调整,这时候我们可以使用CUBE:
SELECT
Color,
SUM(Quantity) AS Quantity
FROM
#T
GROUP BY
Color WITH CUBE;
结果:
我们看到这样就解决了上面的问题,对颜色进行了统计,同时在最后生成了合计数据,但是这里还有一个问题,就是合计那里显示的NULL,这个我们可以用GROUPING函数来解决(GROUPING后续我们会再介绍):
SELECT
CASE
WHEN GROUPING(Color) = 1
THEN '合计'
ELSE
Color
END Color,
SUM(Quantity) AS Quantity
FROM
#T
GROUP BY
Color WITH CUBE;
结果:
这样就达到了我们想要的效果,后续我们会继续介绍关于CUBE的多维使用方式。