SQL 了解 CUBE 和 ROLLUP 之间的区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7053471/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Understanding the differences between CUBE and ROLLUP
提问by krebshack
My assignment asked me to find out "how many invoices are written for each date?"
我的作业要求我找出“每个日期写了多少张发票？”
I was a little stuck and asked my professor for help. She emailed me a query that would answer the question, "How many stoves of each type and version have been built? For a challenge but no extra points, include the total number of stoves."
我有点卡住了，向我的教授寻求帮助。她通过电子邮件向我发送了一个问题，该问题可以回答这个问题，“每种类型和版本的炉灶已经建造了多少？对于一个挑战但没有加分的问题，请包括炉灶的总数。”
This was the query she sent me:
这是她发给我的查询：
SELECT STOVE.Type + STOVE.Version AS 'Type+Version'
, COUNT(*) AS 'The Count'
FROM STOVE
GROUP BY STOVE.Type + STOVE.Version WITH ROLLUP;
So, I tweaked that query until it met my needs. This is what I came up with:
因此，我调整了该查询，直到它满足我的需求。这就是我想出的：
SELECT InvoiceDt
, COUNT(InvoiceNbr) AS 'Number of Invoices'
FROM INVOICE
GROUP BY InvoiceDt WITH ROLLUP
ORDER BY InvoiceDt ASC;
And it returned the following results that I wanted.
它返回了我想要的以下结果。
Anyway, I decided to read up on the ROLLUP clause and started with an article from Microsoft. It said that the ROLLUP clause was similar to the CUBE clause but that it was distinguished from the CUBE clause in the following way:
无论如何，我决定阅读 ROLLUP 子句并从Microsoft的一篇文章开始。它说 ROLLUP 子句类似于 CUBE 子句，但它与 CUBE 子句的区别如下：
- CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.
- ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.
- CUBE 生成一个结果集，显示所选列中所有值组合的聚合。
- ROLLUP 生成一个结果集，显示所选列中值层次结构的聚合。
So, I decided to replace the ROLLUP in my query with CUBE to see what would happen. They produced the same results. I guess that's where I'm getting confused.
因此，我决定将查询中的 ROLLUP 替换为 CUBE，看看会发生什么。他们产生了相同的结果。我想这就是我感到困惑的地方。
It seems like, if you're using the type of query that I am here, that there isn't any practical difference between the two clauses. Is that right? Or, am I not understanding something? I had thought, when I finished reading the Microsoft article, that my results should've been different using the CUBE clause.
看起来，如果您使用的是我在这里的查询类型，那么这两个子句之间没有任何实际区别。那正确吗？或者，我不明白什么？我曾想，当我读完 Microsoft 文章时，使用 CUBE 子句我的结果应该有所不同。
回答by Derek Kromm
You won't see any difference since you're only rolling up a single column. Consider an example where we do
您不会看到任何区别，因为您只滚动了一个列。考虑一个我们做的例子
ROLLUP (YEAR, MONTH, DAY)
ROLLUP (YEAR, MONTH, DAY)
With a ROLLUP, it will have the following outputs:
使用 a ROLLUP，它将具有以下输出：
YEAR, MONTH, DAY
YEAR, MONTH
YEAR
()
With CUBE, it will have the following:
使用CUBE，它将具有以下内容：
YEAR, MONTH, DAY
YEAR, MONTH
YEAR, DAY
YEAR
MONTH, DAY
MONTH
DAY
()
CUBEessentially contains every possible rollup scenario for each node whereas ROLLUPwill keep the hierarchy in tact (so it won't skip MONTH and show YEAR/DAY, whereas CUBEwill)
CUBE基本上包含每个节点的所有可能的汇总方案，而ROLLUP将保持层次结构完整（因此它不会跳过 MONTH 并显示 YEAR/DAY，而CUBE会）
This is why you didn't see a difference since you only had a single column you were rolling up.
这就是为什么您没有看到差异的原因，因为您只有一个要汇总的列。
Hope that helps.
希望有帮助。
回答by Sarath Avanavu
We can understand the difference between ROLLUP and CUBE with a simple example. Consider we have a table which contains the results of quarterly test of students. In certain cases we need to see the total corresponding to the quarter as well as the students. Here is the sample table
我们可以通过一个简单的例子来理解 ROLLUP 和 CUBE 的区别。考虑我们有一个包含学生季度测试结果的表格。在某些情况下，我们需要查看与该季度以及学生相对应的总数。这是示例表
SELECT * INTO #TEMP
FROM
(
SELECT 'Quarter 1' PERIOD,'Amar' NAME ,97 MARKS
UNION ALL
SELECT 'Quarter 1','Ram',88
UNION ALL
SELECT 'Quarter 1','Simi',76
UNION ALL
SELECT 'Quarter 2','Amar',94
UNION ALL
SELECT 'Quarter 2','Ram',82
UNION ALL
SELECT 'Quarter 2','Simi',71
UNION ALL
SELECT 'Quarter 3' ,'Amar',95
UNION ALL
SELECT 'Quarter 3','Ram',83
UNION ALL
SELECT 'Quarter 3','Simi',77
UNION ALL
SELECT 'Quarter 4' ,'Amar',91
UNION ALL
SELECT 'Quarter 4','Ram',84
UNION ALL
SELECT 'Quarter 4','Simi',79
)TAB
1. ROLLUP(Can find total for corresponding to one column)
1. ROLLUP（可以找到对应一列的总数）
(a) Get total score of each student in all quarters.
(a) 获取每个学生在所有季度的总分。
SELECT * FROM #TEMP
UNION ALL
SELECT PERIOD,NAME,SUM(MARKS) TOTAL
FROM #TEMP
GROUP BY NAME,PERIOD
WITH ROLLUP
HAVING PERIOD IS NULL AND NAME IS NOT NULL
// Having is used inorder to emit a row that is the total of all totals of each student
Following is the result of (a)
以下是（a）的结果
(b) Incase you need to get total score of each quarter
(b) 如果您需要获得每个季度的总分
SELECT * FROM #TEMP
UNION ALL
SELECT PERIOD,NAME,SUM(MARKS) TOTAL
FROM #TEMP
GROUP BY PERIOD,NAME
WITH ROLLUP
HAVING PERIOD IS NOT NULL AND NAME IS NULL
Following is the result of (b)
以下是（b）的结果
2. CUBE(Find total for Quarter as well as students in a single shot)
2. CUBE（单次计算Quarter和学生的总数）
SELECT PERIOD,NAME,SUM(MARKS) TOTAL
FROM #TEMP
GROUP BY NAME,PERIOD
WITH CUBE
HAVING PERIOD IS NOT NULL OR NAME IS NOT NULL
Following is the result of CUBE
以下是结果 CUBE
Now you may be wondering about the real time use of ROLLUP and CUBE. Sometimes we need a report in which we need to see the total of each quarter and total of each student in a single shot. Here is an example
现在您可能想知道 ROLLUP 和 CUBE 的实时使用。有时我们需要一份报告，在其中我们需要一次性查看每个季度的总数和每个学生的总数。这是一个例子
I am changing the above CUBE query slightly as we need total for both totals.
我正在稍微更改上面的 CUBE 查询，因为我们需要两个总数的总数。
SELECT CASE WHEN PERIOD IS NULL THEN 'TOTAL' ELSE PERIOD END PERIOD,
CASE WHEN NAME IS NULL THEN 'TOTAL' ELSE NAME END NAME,
SUM(MARKS) MARKS
INTO #TEMP2
FROM #TEMP
GROUP BY NAME,PERIOD
WITH CUBE
DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + PERIOD + ']',
'[' + PERIOD + ']')
FROM (SELECT DISTINCT PERIOD FROM #TEMP2) PV
ORDER BY PERIOD
DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT * FROM
(
SELECT * FROM #TEMP2
) x
PIVOT
(
SUM(MARKS)
FOR [PERIOD] IN (' + @cols + ')
) p;'
EXEC SP_EXECUTESQL @query
Now you will get the following result
现在你会得到以下结果
回答by JeffO
This is because you only have one column that you are grouping by.
这是因为您只有一列作为分组依据。
Add Group by InvoiceDt, InvoiceCountry(or whatever field will give you more data.
添加Group by InvoiceDt, InvoiceCountry（或任何可以为您提供更多数据的字段。
With Cube will give you a Sum for each InvoiceDt and you will get a Sum for each InvoiceCountry.
Cube 将为您提供每个 InvoiceDt 的总和，您将获得每个 InvoiceCountry 的总和。
回答by o0omycomputero0o
You can find more detail about GROUPING SET, CUBE, ROLL UP. TL;DR they just expand GROUP BY + UNION ALL in some ways to get aggregation :)
您可以找到有关 GROUPING SET、CUBE、ROLL UP 的更多详细信息。TL;DR 他们只是以某种方式扩展 GROUP BY + UNION ALL 以获得聚合:)
https://technet.microsoft.com/en-us/library/bb510427(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/bb510427(v=sql.105).aspx
