SQLSERVER中的ALL、PERCENT、CUBE关键字、ROLLUP关键字和GROUPIN

SQLSERVER中的ALL、PERCENT、CUBE关键字、ROLLUP关键字和GROUPING函数 先来创建一个测试表 1 USE [ tempdb ] 2 GO 3 4 CREATE TABLE #temptb(id INT ,NAME VARCHAR ( 200 )) 5 GO 6 7 INSERT INTO [ #temptb ] ( [ id ] , [ NAME ] ) 8 SELECT 1 , ' 中国 '

SQLSERVER中的ALL、PERCENT、CUBE关键字、ROLLUP关键字和GROUPING函数

 先来创建一个测试表

<span> 1</span> <span>USE</span> <span>[</span><span>tempdb</span><span>]</span>
<span> 2</span> <span>GO</span>
<span> 3</span> 
<span> 4</span> <span>CREATE</span> <span>TABLE</span> #temptb(id <span>INT</span> ,NAME <span>VARCHAR</span>(<span>200</span><span>))
</span><span> 5</span> <span>GO</span>
<span> 6</span> 
<span> 7</span> <span>INSERT</span> <span>INTO</span> <span>[</span><span>#temptb</span><span>]</span> ( <span>[</span><span>id</span><span>]</span>, <span>[</span><span>NAME</span><span>]</span><span> )
</span><span> 8</span> <span>SELECT</span> <span>1</span>,<span>'</span><span>中国</span><span>'</span> <span>UNION</span> <span>ALL</span>
<span> 9</span> <span>SELECT</span> <span>2</span>,<span>'</span><span>中国</span><span>'</span> <span>UNION</span> <span>ALL</span>
<span>10</span> <span>SELECT</span> <span>3</span>,<span>'</span><span>英国</span><span>'</span> <span>UNION</span> <span>ALL</span>
<span>11</span> <span>SELECT</span> <span>4</span>,<span>'</span><span>英国</span><span>'</span> <span>UNION</span> <span>ALL</span>
<span>12</span> <span>SELECT</span> <span>5</span>,<span>'</span><span>美国</span><span>'</span> <span>UNION</span> <span>ALL</span>
<span>13</span> <span>SELECT</span> <span>6</span>,<span>'</span><span>美国</span><span>'</span> <span>UNION</span> <span>ALL</span>
<span>14</span> <span>SELECT</span> <span>null</span>, <span>'</span><span>法国</span><span>'</span> <span>UNION</span> <span>ALL</span>
<span>15</span> <span>SELECT</span> <span>8</span>,<span>'</span><span>法国</span><span>'</span> 
<span>16</span> <span>GO</span>
<span>17</span> 
<span>18</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>#temptb</span><span>]</span>
<span>19</span> <span>GO</span>

---

 

先来看一下SELECT语句的语法：

<span>1</span> <span>SELECT</span> <span>[</span><span> ALL | DISTINCT </span><span>]</span> <span>[</span><span> topSubclause </span><span>]</span><span> aliasedExpr 
</span><span>2</span>       <span>[</span><span>{ , aliasedExpr }</span><span>]</span> <span>FROM</span> fromClause <span>[</span><span> WHERE whereClause </span><span>]</span> <span>[</span><span> GROUP BY groupByClause [ HAVING havingClause </span><span>]</span> ] <span>[</span><span> ORDER BY orderByClause </span><span>]</span>
<span>3</span> <span>or</span>
<span>4</span> <span>SELECT</span> VALUE <span>[</span><span> ALL | DISTINCT </span><span>]</span> <span>[</span><span> topSubclause </span><span>]</span> expr <span>FROM</span> fromClause <span>[</span><span> WHERE whereClause </span><span>]</span> <span>[</span><span> GROUP BY groupByClause [ HAVING havingClause </span><span>]</span> ] <span>[</span><span> ORDER BY orderByClause</span>

 

ALL关键字：指定在结果集中可以显示重复的行，这是默认的关键字，也就是说，当您在查询中不使用ALL关键字，默认都已经附加上了ALL这个关键字

例如下面两个SQL语句，实际上是等价的，都会把重复的记录select出来

<span>1</span> <span>--</span><span>这两个语句是等价的</span>
<span>2</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>#temptb</span><span>]</span>
<span>3</span> <span>GO</span>
<span>4</span> <span>--</span><span>-----------------------------------------</span>
<span>5</span> <span>SELECT</span> <span>ALL</span> <span>*</span> <span>FROM</span> <span>[</span><span>#temptb</span><span>]</span>
<span>6</span> <span>GO</span>

如果您需要把唯一值select出来，过滤掉那些重复值需要使用DISTINCT关键字

<span>1</span> <span>SELECT</span> <span>DISTINCT</span>(<span>[</span><span>NAME</span><span>]</span>) <span>FROM</span> <span>[</span><span>#temptb</span><span>]</span>

而当您把SQL语句，字段放在ALL括号中，这时候就会变成一个表达式，例如下面SQL语句

<span>1</span> <span>SELECT</span> <span>ALL</span>(<span>[</span><span>NAME</span><span>]</span><span>+</span><span>'</span><span>您好</span><span>'</span>) <span>AS</span> <span>'</span><span>国别</span><span>'</span> <span>FROM</span> <span>[</span><span>#temptb</span><span>]</span>

处理表重复记录(查询和删除)

在Name相同ID最大的记录，其中有一个SQL语句

<span>1</span> <span>SELECT</span>  <span>*</span>
<span>2</span> <span>FROM</span>    <span>[</span><span>#temptb</span><span>]</span><span> a
</span><span>3</span> <span>WHERE</span>   ID<span>!<all> ( <span>SELECT</span><span>    ID
</span><span>4</span>                   <span>FROM</span>      <span>[</span><span>#temptb</span><span>]</span>
<span>5</span>                   <span>WHERE</span>     Name <span>=</span> a.Name )</all></span>

如果去掉ALL关键字会怎样呢？

因为子查询需要的是一个表达式，所以需要使用ALL关键字把他变为一个表达式，所以要用ALL

 

ALL关键字还可以放在GROUP BY 之后

这里要分两种情况，一种是SQL语句中有where子句的的，另一种是SQL语句中没有where子句的

情况一：

<span>1</span> <span>SELECT</span> <span>AVG</span>(id) <span>FROM</span> <span>[</span><span>#temptb</span><span>]</span> <span>WHERE</span> NAME<span>=</span><span>'</span><span>法国</span><span>'</span> <span>GROUP</span> <span>BY</span> <span>ALL</span><span> NAME
</span><span>2</span> <span>SELECT</span> <span>AVG</span>(id) <span>FROM</span> <span>[</span><span>#temptb</span><span>]</span> <span>WHERE</span> NAME<span>=</span><span>'</span><span>法国</span><span>'</span>  <span>GROUP</span> <span>BY</span> NAME

对于没有符合条件的行的组，这里是没有符合name='法国'，作为聚合值的列值为NULL

如果没有ALL关键字，GROUP BY子句将不显示没有符合条件的行的组

情况二：

<span>1</span> <span>SELECT</span> <span>AVG</span>(id) <span>FROM</span> <span>[</span><span>#temptb</span><span>]</span>  <span>GROUP</span> <span>BY</span> <span>ALL</span><span> NAME
</span><span>2</span> <span>SELECT</span> <span>AVG</span>(id) <span>FROM</span> <span>[</span><span>#temptb</span><span>]</span>  <span>GROUP</span> <span>BY</span>  NAME

当SQL语句中没有where子句的时候，查询出来的结果都是一样的

 

ALL关键字还可以放在UNION之后

<span>1</span> <span>USE</span> <span>[</span><span>GPOSDB</span><span>]</span>
<span>2</span> <span>GO</span>
<span>3</span> <span>INSERT</span> <span>INTO</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>SystemPara</span><span>]</span> ( <span>[</span><span>ParaValue</span><span>]</span>, <span>[</span><span>Name</span><span>]</span>, <span>[</span><span>Description</span><span>]</span><span> )
</span><span>4</span> <span>SELECT</span> <span>'</span><span>nihao</span><span>'</span>,<span>'</span><span>nihao</span><span>'</span>,<span>'</span><span>nihao</span><span>'</span> <span>UNION</span> <span>ALL</span>
<span>5</span> <span>SELECT</span> <span>'</span><span>nihao</span><span>'</span>,<span>'</span><span>nihao</span><span>'</span>,<span>'</span><span>nihao</span><span>'</span> 

 

---

PERCENT关键字

PERCENT关键字需要跟TOP 关键字一起使用

从结果集中输出百分之N行，n必须是介于0~100之间的整数

<span>1</span> <span>SELECT</span> <span>TOP</span> <span>10</span> <span>PERCENT</span> <span>*</span> <span>from</span> <span>[</span><span>#temptb</span><span>]</span>
<span>2</span> <span>GO</span>

上面的SQL语句意思是：从[#temptb]表中输出10%的记录数，因为没有使用order by子句，所以这条记录是随机的

因为[#temptb]表有8条记录，8*10%=0.8 四舍五入之后相当于一条记录

<span>1</span> <span>SELECT</span> <span>TOP</span> <span>30</span> <span>PERCENT</span> <span>*</span> <span>from</span> <span>[</span><span>#temptb</span><span>]</span>
<span>2</span> <span>GO</span>

8*30%=2.4 四舍五入之后相当于三条记录，SQLSERVER在这里就算四舍五入不足三条记录，他也会输出偏大的数，也就是三条记录

 

---

CUBE关键字

CUBE关键字:如果需要在结果集内不仅包含由GROUP BY提供的正常行，还包含汇总行，可以用CUBE关键字。CUBE关键字与GROUP BY一起使用

当使用CUBE关键字的时候，可以使用GROUPING函数来输出一个额外的列，当结果行是正常的行时，返回0；当结果行是汇总行时，返回1。

<span>1</span> <span>SELECT</span>  <span>AVG</span>(id) <span>AS</span> <span>'</span><span>平均值</span><span>'</span>, <span>GROUPING</span>(NAME) <span>AS</span> <span>'</span><span>是否已汇总</span><span>'</span>
<span>2</span> <span>FROM</span>    <span>[</span><span>#temptb</span><span>]</span>
<span>3</span> <span>GROUP</span> <span>BY</span><span> NAME
</span><span>4</span>         <span>WITH</span> CUBE

最后一行显示了GROUP BY的记录有多少行，一共有4行记录，而在汇总行（即最后一行）是否已汇总那列显示1，表示是汇总行

---

Grouping关键字

指示是否聚合 GROUP BY 列表中的指定列表达式。

在结果集中，如果 GROUPING 返回 1 则指示聚合；返回 0 则指示不聚合。

如果指定了 GROUP BY，则 GROUPING 只能用在 SELECT 列表、HAVING 和 ORDER BY 子句中。

http://msdn.microsoft.com/zh-cn/library/ms178544(v=sql.105).aspx

GROUPING 用于区分标准空值和由 ROLLUP、CUBE 或 GROUPING SETS 返回的空值。

作为 ROLLUP、CUBE 或 GROUPING SETS 操作结果返回的 NULL 是 NULL 的特殊应用。

它在结果集内作为列的占位符，表示全体。

 

以下示例将分组 SalesQuota 并聚合 SaleYTD 数量。GROUPING 函数应用于 SalesQuota 列。

<span>1</span> <span>USE</span> <span>[</span><span>AdventureWorks</span><span>]</span><span>;
</span><span>2</span> <span>GO</span>
<span>3</span> <span>SELECT</span>  SalesQuota, <span>SUM</span>(SalesYTD) <span>'</span><span>TotalSalesYTD</span><span>'</span><span>,
</span><span>4</span>         <span>GROUPING</span>(SalesQuota) <span>AS</span> <span>'</span><span>Grouping</span><span>'</span>
<span>5</span> <span>FROM</span><span>    Sales.SalesPerson
</span><span>6</span> <span>GROUP</span> <span>BY</span><span> SalesQuota
</span><span>7</span>         <span>WITH</span><span> ROLLUP;
</span><span>8</span> <span>GO</span>

结果集在 SalesQuota 下面显示两个空值。

第一个 NULL 代表从表中的这一列得到的空值组。

第二个 NULL 位于 ROLLUP 操作所添加的汇总行之中。

汇总行显示所有 SalesQuota 组的 TotalSalesYTD 数量，并以 Grouping 列中的 1 进行指示。

 

---

 

http://msdn.microsoft.com/zh-cn/library/ms191500(v=sql.100).aspx
对简单汇总报表使用 Transact-SQL

生成简单汇总报表的应用程序可使用下列 Transact-SQL 元素：

ROLLUP、CUBE 或 GROUPING SETS 运算符。这些是 SELECT 语句的 GROUP BY 子句的扩展。

COMPUTE 或 COMPUTE BY 运算符。这两种运算符也与 GROUP BY 相关联。

这些运算符生成的结果集中，既包含每个项目的明细行，也包含每个组的汇总行，汇总行显示了该组的聚合合计。

GROUP BY 子句可用于生成只包含各组的聚合而不包含其明细行的结果。

应用程序应使用 Analysis Services，而不是 CUBE、ROLLUP、COMPUTE 或 COMPUTE BY。

特别要注意的是，CUBE 和 ROLLUP 应当只用在无法访问 OLE DB 或 ADO 的环境中，例如脚本或存储过程中。

支持 COMPUTE 和 COMPUTE BY 是为了向后兼容。

应当优先选用 ROLLUP 运算符而非 COMPUTE 或 COMPUTE BY。由 COMPUTE 或 COMPUTE BY 生成的汇总值将作为多个单独的结果集返回，

这些结果集之间还插入了包含各组明细行的结果集；或者作为包含合计的结果集返回，附加在主结果集之后。

处理这些多个结果集将增加应用程序代码的复杂性。服务器游标既不支持 COMPUTE，也不支持 COMPUTE BY。

但 ROLLUP 支持服务器游标。CUBE 和 ROLLUP 将生成单个结果集，其中包含嵌入的小计和合计行。

此外，查询优化器有时还可以为 ROLLUP 生成比为 COMPUTE 和 COMPUTE BY 生成的执行计划更高效的执行计划。

如果使用不带这些运算符的 GROUP BY，将返回单个结果集，其中每组对应一行，行中包含该组的聚合小计。结果集中没有明细行。

 

---

SQLSERVER中Cube 、RollUp的用法

Cube 、RollUp可以对查询的数据进行汇总，在数据统计中经常用到，尤其是做报表时，用在Select语句中

下面就对两种统计方式进行对比

SQL脚本如下：

<span> 1</span> <span>USE</span> <span>[</span><span>tempdb</span><span>]</span>
<span> 2</span> <span>GO</span>
<span> 3</span> <span>CREATE</span> <span>TABLE</span><span> t_test
</span><span> 4</span> <span>(
</span><span> 5</span>   id <span>INT</span><span> ,
</span><span> 6</span>   productName <span>VARCHAR</span>(<span>200</span><span>) ,
</span><span> 7</span>   price <span>MONEY</span><span> ,
</span><span> 8</span>   num <span>INT</span><span> ,
</span><span> 9</span>   amount <span>INT</span><span> ,
</span><span>10</span>   operatedate <span>DATETIME</span>
<span>11</span> <span>)
</span><span>12</span> <span>GO</span>
<span>13</span> 
<span>14</span> <span>--</span><span>插入随机数据</span>
<span>15</span> <span>DECLARE</span> <span>@i</span> <span>INT</span> 
<span>16</span> <span>DECLARE</span> <span>@rand</span> <span>MONEY</span>
<span>17</span> <span>DECLARE</span> <span>@date</span> <span>DATETIME</span>
<span>18</span> <span>DECLARE</span> <span>@index</span> <span>INT</span> 
<span>19</span> <span>DECLARE</span> <span>@DateBase</span> <span>INT</span> 
<span>20</span> <span>SET</span> <span>@date</span> <span>=</span> <span>'</span><span>2012-10-23</span><span>'</span>
<span>21</span> <span>SET</span> <span>@i</span> <span>=</span> <span>1</span>
<span>22</span> <span>WHILE</span> ( <span>@i</span> <span> <span>18</span><span> ) 
</span><span>23</span>     <span>BEGIN</span>
<span>24</span>         <span>SET</span> <span>@rand</span> <span>=</span> <span>RAND</span>() <span>*</span> <span>20</span>
<span>25</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>26</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>27</span>  
<span>28</span>         <span>INSERT</span>  <span>INTO</span><span> t_test ( id, productName, price, num, amount, operatedate )
</span><span>29</span>         <span>VALUES</span>  ( <span>@i</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>@rand</span>, <span>100</span><span>,
</span><span>30</span>                   <span>@rand</span> <span>*</span> <span>100</span>, <span>@date</span> <span>+</span> <span>@DateBase</span><span> )
</span><span>31</span>         <span>SET</span> <span>@i</span> <span>=</span> <span>@i</span> <span>+</span> <span>1</span>
<span>32</span>     <span>END</span>
<span>33</span>  
<span>34</span>  
<span>35</span> <span>SELECT</span>  <span>*</span>  <span>FROM</span>    t_test</span>

 

 分别用两种方式统计：

<span> 1</span> <span>--</span><span>分别用两种方式统计：</span>
<span> 2</span>  
<span> 3</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> 4</span>              <span>ELSE</span> <span>CONVERT</span>(<span>VARCHAR</span>(<span>10</span>), operatedate, <span>120</span><span>)
</span><span> 5</span>         <span>END</span> <span>AS</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> 6</span>                         <span>ELSE</span><span> productName
</span><span> 7</span>                    <span>END</span> <span>AS</span> 产品名称, <span>SUM</span>(amount) <span>/</span> <span>SUM</span>(num) <span>AS</span> 平均价格, <span>SUM</span>(num) <span>AS</span><span> 数量,
</span><span> 8</span>         <span>SUM</span>(amount) <span>AS</span><span> 金额
</span><span> 9</span> <span>FROM</span><span>    t_test
</span><span>10</span> <span>GROUP</span> <span>BY</span> operatedate, productName  <span>WITH</span><span> ROLLUP;   
</span><span>11</span> <span>--</span><span>-----------------------------------------------------------------</span>
<span>12</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>13</span>              <span>ELSE</span> <span>CONVERT</span>(<span>VARCHAR</span>(<span>10</span>), operatedate, <span>120</span><span>)
</span><span>14</span>         <span>END</span> <span>AS</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>15</span>                         <span>ELSE</span><span> productName
</span><span>16</span>                    <span>END</span> <span>AS</span> 产品名称, <span>SUM</span>(amount) <span>/</span> <span>SUM</span>(num) <span>AS</span> 平均价格, <span>SUM</span>(num) <span>AS</span><span> 数量,
</span><span>17</span>         <span>SUM</span>(amount) <span>AS</span><span> 金额
</span><span>18</span> <span>FROM</span><span>    t_test
</span><span>19</span> <span>GROUP</span> <span>BY</span> operatedate, productName <span>WITH</span> CUBE; 

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

<span>1</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>--</span><span>用GROUPING得出是否是汇总行，这个例子里最后一行是汇总行</span>
<span>2</span>              <span>ELSE</span> <span>CONVERT</span>(<span>VARCHAR</span>(<span>10</span>), operatedate, <span>120</span><span>)
</span><span>3</span>         <span>END</span> <span>AS</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>4</span>                         <span>ELSE</span><span> productName
</span><span>5</span>                    <span>END</span> <span>AS</span> 产品名称, <span>SUM</span>(amount) <span>/</span> <span>SUM</span>(num) <span>AS</span> 平均价格, <span>SUM</span>(num) <span>AS</span><span> 数量,
</span><span>6</span>         <span>SUM</span>(amount) <span>AS</span><span> 金额
</span><span>7</span> <span>FROM</span><span>    t_test
</span><span>8</span> <span>GROUP</span> <span>BY</span> operatedate, productName  <span>WITH</span> ROLLUP;   <span>--</span><span>因为operatedate和productName字段都在GROUPING函数里统计是否汇总，所以GROUP BY后面就需要加operatedate和productName这两个字段</span>

 

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

ROLLUP和CUBE的区别就是： ROLLUP 只会去统计group by 后面的第一个字段每个分组的小计和第一个字段的总计
 
Grouping(字段名) 用来区分当前行是不是小计产生的行，  Grouping(字段名)=1 说明是统计行，Grouping(字段名)=0 说明是表中行

可以用在case,where 后面

http://www.2cto.com/database/201210/163455.html

---

另外一个例子

SQL脚本如下：

<span> 1</span> <span>USE</span> <span>[</span><span>tempdb</span><span>]</span>
<span> 2</span> <span>GO</span>
<span> 3</span> <span>CREATE</span> <span>TABLE</span> Sales (EmpId <span>INT</span>, Yr <span>INT</span>, Sales <span>MONEY</span><span>)
</span><span> 4</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>1</span>, <span>2005</span>, <span>12000</span><span>)
</span><span> 5</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>1</span>, <span>2006</span>, <span>18000</span><span>)
</span><span> 6</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>1</span>, <span>2007</span>, <span>25000</span><span>)
</span><span> 7</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>2</span>, <span>2005</span>, <span>15000</span><span>)
</span><span> 8</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>2</span>, <span>2006</span>, <span>6000</span><span>)
</span><span> 9</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>3</span>, <span>2006</span>, <span>20000</span><span>)
</span><span>10</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>3</span>, <span>2007</span>, <span>24000</span><span>)
</span><span>11</span> 
<span>12</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Sales</span><span>]</span>

View Code

ROLLUP

<span>1</span> <span>SELECT</span> EmpId, Yr, <span>SUM</span>(Sales) <span>AS</span><span> Sales
</span><span>2</span> <span>FROM</span><span> Sales
</span><span>3</span> <span>GROUP</span> <span>BY</span> EmpId, Yr <span>WITH</span> ROLLUP

CUBE

<span>1</span> <span>SELECT</span> EmpId, Yr, <span>SUM</span>(Sales) <span>AS</span><span> Sales
</span><span>2</span> <span>FROM</span><span> Sales
</span><span>3</span> <span>GROUP</span> <span>BY</span> EmpId, Yr <span>WITH</span> CUBE

CUBE比ROLLUP多了年份的统计，统计了2005、2006、2007年的销售额

可以用下图来表示

ROLLUP

 

CUBE

 http://blogs.msdn.com/b/craigfr/archive/2007/10/11/grouping-sets-in-sql-server-2008.aspx

 

---

验证CUBE和ROLLUP 的区别

ROLLUP和CUBE的区别就是： ROLLUP 只会去统计group by 后面的第一个字段每个分组的小计和第一个字段的总计

我们修改一下上面那个实验

<span> 1</span> <span>USE</span> <span>[</span><span>tempdb</span><span>]</span>
<span> 2</span> <span>GO</span>
<span> 3</span> <span>CREATE</span> <span>TABLE</span> Sales (EmpId <span>INT</span>,productName <span>VARCHAR</span>(<span>200</span>), Yr <span>INT</span>, Sales <span>MONEY</span><span>)
</span><span> 4</span> <span>GO</span>
<span> 5</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>1</span>,<span>'</span><span>product2</span><span>'</span>, <span>2005</span>, <span>12000</span><span>)
</span><span> 6</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>1</span>,<span>'</span><span>product1</span><span>'</span>, <span>2005</span>, <span>18000</span><span>)
</span><span> 7</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>1</span>,<span>'</span><span>product0</span><span>'</span>, <span>2006</span>, <span>25000</span><span>)
</span><span> 8</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>1</span>,<span>'</span><span>product2</span><span>'</span>, <span>2007</span>, <span>15000</span><span>)
</span><span> 9</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>2</span>,<span>'</span><span>product1</span><span>'</span>, <span>2005</span>, <span>60000</span><span>)
</span><span>10</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>2</span>,<span>'</span><span>product1</span><span>'</span>, <span>2006</span>, <span>22000</span><span>)
</span><span>11</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>2</span>,<span>'</span><span>product0</span><span>'</span>, <span>2007</span>, <span>24000</span><span>)
</span><span>12</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>3</span>,<span>'</span><span>product0</span><span>'</span>, <span>2005</span>, <span>32000</span><span>)
</span><span>13</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>3</span>,<span>'</span><span>product2</span><span>'</span>, <span>2006</span>, <span>42000</span><span>)
</span><span>14</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>3</span>,<span>'</span><span>product0</span><span>'</span>, <span>2007</span>, <span>24000</span><span>)
</span><span>15</span> <span>GO</span>
<span>16</span> 
<span>17</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Sales</span><span>]</span>

View Code

 ROLLUP

<span>1</span> <span>SELECT</span> EmpId, Yr,<span>[</span><span>productName</span><span>]</span>, <span>SUM</span>(Sales) <span>AS</span><span> Sales
</span><span>2</span> <span>FROM</span><span> Sales
</span><span>3</span> <span>GROUP</span> <span>BY</span> EmpId, Yr,<span>[</span><span>productName</span><span>]</span> <span>WITH</span> ROLLUP

CUBE

<span>1</span> <span>SELECT</span> EmpId, Yr,<span>[</span><span>productName</span><span>]</span>, <span>SUM</span>(Sales) <span>AS</span><span> Sales
</span><span>2</span> <span>FROM</span><span> Sales
</span><span>3</span> <span>GROUP</span> <span>BY</span> EmpId, Yr,<span>[</span><span>productName</span><span>]</span> <span>WITH</span> CUBE

可以看到CUBE除了统计EmpId字段之外，还统计了GROUP BY后面的Yr和productName这两个字段

而ROLLUP只统计了EmpId这个字段

---

 

总结

这些关键字和函数对平时用于统计的应用程序都非常有用，如果大家对这些函数功能都很熟悉的话，在开发当中一定能够得心应手

另外，个人觉得PERCENT关键字可以应用在分页上

 

如有不对的地方，欢迎大家拍砖哦o(∩_∩)o