谁能告诉我到底SQL的WITHCUBE，和WITHROLLUP实现什么样的效果，区别是什么，要是能举例说明，感激不尽

The ROLLUP operator is useful in generating reports that contain subtotals and totals. The ROLLUP operator generates a result set that is similar to the result sets generated by the CUBE operator. For more information, see Summarizing Data Using CUBE.

The differences between CUBE and ROLLUP are:

CUBE generates a result set showing aggregates for all combinations of values in the selected columns.

ROLLUP generates a result set showing aggregates for a hierarchy of values in the selected columns.

For example, a simple table Inventory contains:

Item ColorQuantity

-------------------- -------------------- --------------------------

TableBlue 124

TableRed 223

ChairBlue 101

ChairRed 210

This query generates a subtotal report:

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 ROLLUP

Item ColorQtySum

-------------------- -------------------- --------------------------

ChairBlue 101.00

ChairRed 210.00

ChairALL 311.00

TableBlue 124.00

TableRed 223.00

TableALL 347.00

ALL ALL 658.00

(7 row(s) affected)

If the ROLLUP keyword in the query is changed to CUBE, the CUBE result set is the same, except these two additional rows are returned at the end:

ALL Blue 225.00

ALL Red 433.00

The CUBE operation generated rows for possible combinations of values from both Item and Color. For example, not only does CUBE report all possible combinations of Color values combined with the Item value Chair (Red, Blue, and Red + Blue), it also reports all possible combinations of Item values combined with the Color value Red (Chair, Table, and Chair + Table).

For each value in the columns on the right in the GROUP BY clause, the ROLLUP operation does not report all possible combinations of values from the column (or columns) on the left. For example, ROLLUP does not report all the possible combinations of Item values for each Color value.

以上是Books Online中的原文.

两者都是配合Group by进行统计,CUBE会对Group by后的每个字段值进行组合统计,而对于ROLLUP,Group by后靠左的字段不会对靠右的字段值产生所有可能的组合统计.(翻译水平有限,汗一个!看例子意会就行)

cube的全称是多维立方体，就是在不同位置存放数据的立方体，主要是用于支持联机分析应用（OLAP）。Cube的使用就是在处理数据时，把所需要的数据进行打包，尤其针对要处理的数据很大的时候，比如FineBI的FineCube，可以避免建模，数据处理起来会很快。希望可以帮到你。

---