SqlServer中Cube RollUp的用法

SqlServer中Cube RollUp的用法

 

Cube 、RollUp可以对查询的数据进行汇总，在数据统计中经常用到，尤其是做报表时，用在Select语句中，下面就对两种统计方式进行对比：

 

先做准备工作：

  www.2cto.com  

----插入随机数据

DECLARE @i INT 

DECLARE @rand MONEY

DECLARE @date DATETIME

DECLARE @index INT 

DECLARE @DateBase INT 

SET @date = '2012-10-23'

SET @i = 1

WHILE ( @i < 18 ) 

    BEGIN

        SET @rand = RAND() * 20

        SET @index = CAST(RAND() * 3 AS INT)

        SET @DateBase = CAST(RAND() * 10 AS INT)

 

        INSERT  INTO t_test

                ( 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 t_test

  www.2cto.com  

 

分别用两种方式统计：

 

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    t_test

GROUP BY  operatedate,productName

WITH ROLLUP  /*WITH Cube*/  

  www.2cto.com  



 

CUBE 会对所有的分组字段进行统计，如上例，先对日期求小计，也就是统计每天的产品总金额，然后统计每个产品的总金额，最后给出总的合计。

 

ROLLUP 按照分组顺序，先对第一个字段operatedate分组，在组内进行统计，最后给出合计。

 

区别就是： ROLLUP 不会去统计group by 后面的第一个字段的小计

 

Grouping(字段名) 用来区分当前行是不是小计产生的行，  Grouping(字段名)=1 说明是统计行，Grouping(字段名)=0 说明是表中行，可以用在case,where 后面