使用 CUBE 汇总数据

原谅链接：https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2005/ms175939(v=sql.90)

CUBE 运算符生成的结果集是多维数据集。多维数据集是事实数据（即记录个别事件的数据）的扩展。扩展是基于用户要分析的列建立的。这些列称为维度。多维数据集是结果集，其中包含各维度的所有可能组合的交叉表格。

CUBE 运算符在 SELECT 语句的 GROUP BY 子句中指定。该语句的选择列表包含维度列和聚合函数表达式。GROUP BY 指定了维度列和关键字 WITH CUBE。结果集包含维度列中各值的所有可能组合，以及与这些维度值组合相匹配的基础行中的聚合值。

CREATE TABLE Inventory
(
	Item char(10) NULL,
	Color char(10) NULL,
	Quantity int NULL
)
INSERT INTO Inventory
VALUES
('Table','Blue','124'),
('Table','Red','223'),
('Chair','Blue','101'),
('Chair','Red','210')

SELECT Item,Color,SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item,Color WITH CUBE


SELECT Item,Color,SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY CUBE(Item,Color)


SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item WITH CUBE

GO
CREATE VIEW InvCube AS
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE

SELECT *
FROM InvCube
WHERE Item = 'Chair'
  AND Color = 'ALL'