GROUPING SETS、CUBE、ROLLUP

其实还是写一个Demo 比较好

USE tempdb
IF OBJECT_ID( 'dbo.T1' , 'U'
            )IS NOT NULL
    BEGIN
        DROP TABLE dbo.T1;
    END;
GO

CREATE TABLE dbo.T1
(
id INT ,
productName VARCHAR(200) ,
price MONEY ,
num INT ,
amount INT ,
operatedate DATETIME
)
GO

DECLARE @i INT
DECLARE @rand MONEY
DECLARE @date DATETIME
DECLARE @index INT
DECLARE @DateBase INT
SET @date = GETDATE()
SET @i = 1
WHILE ( @i < 15 )
BEGIN
SET @rand = RAND() * 20
SET @index = CAST(RAND() * 3 AS INT)
SET @DateBase = CAST(RAND() * 10 AS INT)

INSERT  INTO dbo.T1 ( id, productName, price, num, amount, operatedate )
VALUES  ( @i, 'product' + CAST (@index AS VARCHAR(10)), @rand, 100,@rand * 100, @date + @DateBase )
SET @i = @i + 1
 END

SELECT  *  FROM dbo.T1
order by operatedate

结果集差不多是这样样子，按照时间排序。

跑一句关于ROLLUP和一句CUBE

--ROLLUP
SELECT  CASE WHEN GROUPING(operatedate) = 1 THEN '小计'
               ELSE CONVERT(VARCHAR(10), operatedate, 120)
          END AS 日期, CASE WHEN GROUPING(productName) = 1 THEN '小计'
                          ELSE productName
                     END AS 产品名称, SUM(amount) / SUM(num) AS 平均价格, SUM(num) AS 数量,
          SUM(amount) AS 金额
  FROM    dbo.T1
GROUP BY ROLLUP(operatedate, productName)
ORDER BY  '日期','产品名称';

--CUBE
SELECT  CASE WHEN GROUPING(operatedate) = 1 THEN '小计'
              ELSE CONVERT(VARCHAR(10), operatedate, 120)
         END AS 日期, CASE WHEN GROUPING(productName) = 1 THEN '小计'
                         ELSE productName
                    END AS 产品名称, SUM(amount) / SUM(num) AS 平均价格, SUM(num) AS 数量,
         SUM(amount) AS 金额
FROM    dbo.T1
GROUP BY CUBE (operatedate, productName)
ORDER BY  '日期','产品名称';

为了方便显示是，都按照'日期','产品名称' 进行了排序。贴入Excel 里方便查看结果

可以看到CUB 会多产生关于产品名称的合计。

 

不要使用以下的两个子句，请替换成GROUP BY CUBE ( );GROUP BY ROLLUP ( );

WITH CUBE

后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。指定结果集内不仅包含由 GROUP BY 提供的行，同时还包含汇总行。 GROUP BY 汇总行针对每个可能的组和子组组合在结果集内返回。 使用 GROUPING 函数可确定结果集内的空值是否为 GROUP BY 汇总值。

结果集内的汇总行数取决于 GROUP BY 子句内包含的列数。 由于 CUBE 返回每个可能的组和子组组合，因此不论在列分组时指定使用什么顺序，行数都相同。

WITH ROLLUP

后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。指定结果集内不仅包含由 GROUP BY 提供的行，同时还包含汇总行。 按层次结构顺序，从组内的最低级别到最高级别汇总组。 组的层次结构取决于列分组时指定使用的顺序。 更改列分组的顺序会影响在结果集内生成的行数。

GROUP BY (Transact-SQL)

关于GROUPING SETS 可以参看CareySon 所写的Blog

T-SQL中的GROUP BY GROUPING SETS