SqlServer中Cube RollUp 的用法

Cube 、RollUp可以对查询的数据进行汇总，在数据统计中经常用到，尤其是做报表时，用在Select语句中，下面就对两种统计方式进行对比： 先做准备工作： View Code -- --插入随机数据 DECLARE @i INT DECLARE @rand MONEY DECLARE @date DATETIME DECLARE @ind

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

先做准备工作：

View Code

<span>--</span><span>--插入随机数据</span>
<span>DECLARE</span> <span>@i</span> <span>INT</span> 
<span>DECLARE</span> <span>@rand</span> <span>MONEY</span>
<span>DECLARE</span> <span>@date</span> <span>DATETIME</span>
<span>DECLARE</span> <span>@index</span> <span>INT</span> 
<span>DECLARE</span> <span>@DateBase</span> <span>INT</span> 
<span>SET</span> <span>@date</span> <span>=</span> <span>'</span><span>2012-10-23</span><span>'</span>
<span>SET</span> <span>@i</span> <span>=</span> <span>1</span>
<span>WHILE</span> ( <span>@i</span> <span> <span>18</span><span> ) 
    </span><span>BEGIN</span>
        <span>SET</span> <span>@rand</span> <span>=</span> <span>RAND</span>() <span>*</span> <span>20</span>
        <span>SET</span> <span>@index</span> <span>=</span> <span>CAST</span>(<span>RAND</span>() <span>*</span> <span>3</span> <span>AS</span> <span>INT</span><span>)
        </span><span>SET</span> <span>@DateBase</span> <span>=</span> <span>CAST</span>(<span>RAND</span>() <span>*</span> <span>10</span> <span>AS</span> <span>INT</span><span>)

        </span><span>INSERT</span>  <span>INTO</span><span> t_test
                ( id ,
                  productName ,
                  price ,
                  num ,
                  amount ,
                  operatedate  
                
                )
        </span><span>VALUES</span>  ( <span>@i</span><span> ,
                  </span><span>'</span><span>product</span><span>'</span> <span>+</span> <span>CAST</span> (<span>@index</span> <span>AS</span> <span>VARCHAR</span>(<span>10</span><span>)) ,
                  </span><span>@rand</span><span> ,
                  </span><span>100</span><span> ,
                  </span><span>@rand</span> <span>*</span> <span>100</span><span> ,
                  </span><span>@date</span> <span>+</span> <span>@DateBase</span><span>          
                )
        </span><span>SET</span> <span>@i</span> <span>=</span> <span>@i</span> <span>+</span> <span>1</span>
    <span>END</span>

<span>SELECT</span> <span>*</span> <span>FROM</span><span> t_test


分别用两种方式统计：


按 Ctrl</span><span>+</span><span>C 复制代码
</span><span>View</span><span> Code 

</span><span>SELECT</span>  
        <span>CASE</span> <span>WHEN</span> <span>GROUPING</span>(operatedate) <span>=</span> <span>1</span> <span>THEN</span> <span>'</span><span>小计</span><span>'</span>
             <span>ELSE</span> <span>CONVERT</span>(<span>VARCHAR</span>(<span>10</span>), operatedate, <span>120</span><span>)
        </span><span>END</span> <span>AS</span><span> 日期 ,
        </span><span>CASE</span> <span>WHEN</span> <span>GROUPING</span>(productName) <span>=</span> <span>1</span> <span>THEN</span> <span>'</span><span>小计</span><span>'</span>
             <span>ELSE</span><span> productName
        </span><span>END</span> <span>AS</span><span> 产品名称 ,
        </span><span>SUM</span>(amount) <span>/</span> <span>SUM</span>(num) <span>AS</span><span> 平均价格 ,
        </span><span>SUM</span>(num) <span>AS</span><span> 数量 ,
        </span><span>SUM</span>(amount) <span>AS</span><span> 金额
</span><span>FROM</span><span>    t_test
</span><span>GROUP</span> <span>BY</span><span>  operatedate,productName
</span><span>WITH</span> ROLLUP  <span>/*</span><span>WITH Cube</span><span>*/</span><span>
按 Ctrl</span><span>+</span>C 复制代码</span>

 

         

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

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

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

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