Mikel
[转载]SQL Server 2008中增强的汇总技巧 – 邀月 – 博客园.
SQL Server 2008中对汇总有明显的增强，有点像Oracle的语法了。请看下面四个例子：
假定场景如下：某几位员工在不同时间参加了不同的项目，获取了相应的收入，现在需要按各种分类进行统计。
基本表如下：
<span class="pln">USE testDb2GOIF NOT OBJECT_ID</span><span class="pun">(</span><span class="str">'tb_Income'</span><span class="pun">)</span><span class="pln"> IS NULLDROP TABLE </span><span class="pun">[</span><span class="pln">tb_Income</span><span class="pun">]</span><span class="pln"></span><span class="com">/****** Object: Table [dbo].[tb_Income] Script Date: 2012/4/5 8:19:21 ******/</span><span class="pln">CREATE TABLE </span><span class="pun">[</span><span class="pln">dbo</span><span class="pun">].[</span><span class="pln">tb_Income</span><span class="pun">](</span><span class="pln"></span><span class="pun">[</span><span class="typ">TeamID</span><span class="pun">]</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> </span><span class="kwd">not</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">,</span><span class="pln"></span><span class="pun">[</span><span class="typ">PName</span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="typ">Nvarchar</span><span class="pun">](</span><span class="lit">20</span><span class="pun">)</span><span class="pln"> NOT NULL</span><span class="pun">,</span><span class="pln"></span><span class="pun">[</span><span class="typ">CYear</span><span class="pun">]</span><span class="pln"> </span><span class="typ">Smallint</span><span class="pln"> NOT NULL</span><span class="pun">,</span><span class="pln"></span><span class="pun">[</span><span class="typ">CMonth</span><span class="pun">]</span><span class="pln"> </span><span class="typ">TinyInt</span><span class="pln"> NOT NULL</span><span class="pun">,</span><span class="pln"></span><span class="pun">[</span><span class="typ">CMoney</span><span class="pun">]</span><span class="pln"> </span><span class="typ">Decimal</span><span class="pln"> </span><span class="pun">(</span><span class="lit">10</span><span class="pun">,</span><span class="lit">2</span><span class="pun">)</span><span class="pln"> </span><span class="typ">Not</span><span class="pln"> </span><span class="typ">Null</span><span class="pln"></span><span class="pun">)</span><span class="pln">GOINSERT </span><span class="pun">[</span><span class="pln">dbo</span><span class="pun">].[</span><span class="pln">tb_Income</span><span class="pun">]</span><span class="pln">SELECT </span><span class="lit">1</span><span class="pun">,</span><span class="str">'胡一刀'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">2</span><span class="pun">,</span><span class="lit">5600</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">1</span><span class="pun">,</span><span class="str">'胡一刀'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">1</span><span class="pun">,</span><span class="lit">5678</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">1</span><span class="pun">,</span><span class="str">'胡一刀'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">3</span><span class="pun">,</span><span class="lit">6798</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">2</span><span class="pun">,</span><span class="str">'胡一刀'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">4</span><span class="pun">,</span><span class="lit">7800</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">2</span><span class="pun">,</span><span class="str">'胡一刀'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">5</span><span class="pun">,</span><span class="lit">8899</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">3</span><span class="pun">,</span><span class="str">'胡一刀'</span><span class="pun">,</span><span class="lit">2012</span><span class="pun">,</span><span class="lit">8</span><span class="pun">,</span><span class="lit">8877</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">1</span><span class="pun">,</span><span class="str">'苗人凤'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">1</span><span class="pun">,</span><span class="lit">3455</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">1</span><span class="pun">,</span><span class="str">'苗人凤'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">2</span><span class="pun">,</span><span class="lit">4567</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">2</span><span class="pun">,</span><span class="str">'苗人凤'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">3</span><span class="pun">,</span><span class="lit">5676</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">3</span><span class="pun">,</span><span class="str">'苗人凤'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">4</span><span class="pun">,</span><span class="lit">5600</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">2</span><span class="pun">,</span><span class="str">'苗人凤'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">5</span><span class="pun">,</span><span class="lit">6788</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">2</span><span class="pun">,</span><span class="str">'苗人凤'</span><span class="pun">,</span><span class="lit">2012</span><span class="pun">,</span><span class="lit">6</span><span class="pun">,</span><span class="lit">5679</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">2</span><span class="pun">,</span><span class="str">'苗人凤'</span><span class="pun">,</span><span class="lit">2012</span><span class="pun">,</span><span class="lit">7</span><span class="pun">,</span><span class="lit">6785</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">2</span><span class="pun">,</span><span class="str">'张无忌'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">2</span><span class="pun">,</span><span class="lit">5600</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">2</span><span class="pun">,</span><span class="str">'张无忌'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">3</span><span class="pun">,</span><span class="lit">2345</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">2</span><span class="pun">,</span><span class="str">'张无忌'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">5</span><span class="pun">,</span><span class="lit">12000</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">3</span><span class="pun">,</span><span class="str">'张无忌'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">4</span><span class="pun">,</span><span class="lit">23456</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">3</span><span class="pun">,</span><span class="str">'张无忌'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">6</span><span class="pun">,</span><span class="lit">4567</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">1</span><span class="pun">,</span><span class="str">'张无忌'</span><span class="pun">,</span><span class="lit">2012</span><span class="pun">,</span><span class="lit">7</span><span class="pun">,</span><span class="lit">6789</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">1</span><span class="pun">,</span><span class="str">'张无忌'</span><span class="pun">,</span><span class="lit">2012</span><span class="pun">,</span><span class="lit">8</span><span class="pun">,</span><span class="lit">9998</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">3</span><span class="pun">,</span><span class="str">'赵半山'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">7</span><span class="pun">,</span><span class="lit">6798</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">3</span><span class="pun">,</span><span class="str">'赵半山'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">10</span><span class="pun">,</span><span class="lit">10000</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">3</span><span class="pun">,</span><span class="str">'赵半山'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">9</span><span class="pun">,</span><span class="lit">12021</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">2</span><span class="pun">,</span><span class="str">'赵半山'</span><span class="pun">,</span><span class="lit">2012</span><span class="pun">,</span><span class="lit">11</span><span class="pun">,</span><span class="lit">8799</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">1</span><span class="pun">,</span><span class="str">'赵半山'</span><span class="pun">,</span><span class="lit">2012</span><span class="pun">,</span><span class="lit">12</span><span class="pun">,</span><span class="lit">10002</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">3</span><span class="pun">,</span><span class="str">'令狐冲'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">8</span><span class="pun">,</span><span class="lit">7896</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">3</span><span class="pun">,</span><span class="str">'令狐冲'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">9</span><span class="pun">,</span><span class="lit">7890</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">2</span><span class="pun">,</span><span class="str">'令狐冲'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">10</span><span class="pun">,</span><span class="lit">7799</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">2</span><span class="pun">,</span><span class="str">'令狐冲'</span><span class="pun">,</span><span class="lit">2011</span><span class="pun">,</span><span class="lit">11</span><span class="pun">,</span><span class="lit">9988</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">2</span><span class="pun">,</span><span class="str">'令狐冲'</span><span class="pun">,</span><span class="lit">2012</span><span class="pun">,</span><span class="lit">9</span><span class="pun">,</span><span class="lit">34567</span><span class="pln"></span><span class="kwd">union</span><span class="pln"> ALL SELECT </span><span class="lit">3</span><span class="pun">,</span><span class="str">'令狐冲'</span><span class="pun">,</span><span class="lit">2012</span><span class="pun">,</span><span class="lit">12</span><span class="pun">,</span><span class="lit">5609</span><span class="pln">GO</span> |
数据如下：
<span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM tb_Income</span><span class="com">/*TeamID PName CYear CMonth CMoney胡一刀 2011 2 5600.00胡一刀 2011 1 5678.00胡一刀 2011 3 6798.00胡一刀 2011 4 7800.00胡一刀 2011 5 8899.00胡一刀 2012 8 8877.00苗人凤 2011 1 3455.00苗人凤 2011 2 4567.00苗人凤 2011 3 5676.00苗人凤 2011 4 5600.00苗人凤 2011 5 6788.00苗人凤 2012 6 5679.00苗人凤 2012 7 6785.00张无忌 2011 2 5600.00张无忌 2011 3 2345.00张无忌 2011 5 12000.00张无忌 2011 4 23456.00张无忌 2011 6 4567.00张无忌 2012 7 6789.00张无忌 2012 8 9998.00赵半山 2011 7 6798.00赵半山 2011 10 10000.00赵半山 2011 9 12021.00赵半山 2012 11 8799.00赵半山 2012 12 10002.00令狐冲 2011 8 7896.00令狐冲 2011 9 7890.00令狐冲 2011 10 7799.00令狐冲 2011 11 9988.00令狐冲 2012 9 34567.00令狐冲 2012 12 5609.00*/</span> |
一、使用CUBE汇总数据（http://msdn.microsoft.com/en-us/library/bb522495%28v=sql.105%29.aspx）
小试牛刀，
<span class="com">/*********使用CUBE汇总数据***************/</span><span class="pln"></span><span class="com">/********* 3w@live.cn 邀月***************/</span><span class="pln">SELECT </span><span class="typ">TeamID</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="pun">小组</span><span class="pln">ID</span><span class="pun">,</span><span class="pln">SUM</span><span class="pun">(</span><span class="typ">CMoney</span><span class="pun">)</span><span class="pln"> </span><span class="pun">总收入</span><span class="pln">FROM tb_IncomeGROUP BY CUBE </span><span class="pun">(</span><span class="typ">TeamID</span><span class="pun">)</span><span class="pln"></span><span class="pun">----</span><span class="pln">ORDER BY </span><span class="typ">TeamID</span><span class="pln"> desc</span> |
改进查询：
<span class="pln">SELECT </span><span class="typ">TeamID</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="pun">小组</span><span class="pln">ID</span><span class="pun">,</span><span class="typ">PName</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="pun">姓名,</span><span class="pln">SUM</span><span class="pun">(</span><span class="typ">CMoney</span><span class="pun">)</span><span class="pln"> </span><span class="pun">总收入</span><span class="pln">FROM tb_IncomeGROUP BY CUBE </span><span class="pun">(</span><span class="typ">TeamID</span><span class="pun">,</span><span class="typ">PName</span><span class="pun">)</span> |
二、使用ROLLUP汇总数据（http://msdn.microsoft.com/en-us/library/bb522495%28v=sql.105%29.aspx）
<span class="com">/*********使用ROLLUP汇总数据***************/</span><span class="pln"></span><span class="com">/********* 3w@live.cn 邀月***************/</span><span class="pln">SELECT </span><span class="typ">TeamID</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="pun">小组</span><span class="pln">ID</span><span class="pun">,</span><span class="typ">PName</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="pun">姓名,</span><span class="pln">SUM</span><span class="pun">(</span><span class="typ">CMoney</span><span class="pun">)</span><span class="pln"> </span><span class="pun">总收入</span><span class="pln">FROM tb_IncomeGROUP BY ROLLUP </span><span class="pun">(</span><span class="typ">TeamID</span><span class="pun">,</span><span class="typ">PName</span><span class="pun">)</span> |
注意：使用Rollup与指定的聚合列的顺序有关。
三、使用Grouping Sets创建自定义汇总数据（http://msdn.microsoft.com/en-us/library/bb522495%28v=sql.105%29.aspx）
除了Cube和Rollup，还有更加灵活强大的自定义集合汇总－－Grouping Sets
<span class="com">/*********使用Grouping Sets创建自定义汇总数据***************/</span><span class="pln"></span><span class="com">/********* 3w@live.cn 邀月***************/</span><span class="pln">SELECT </span><span class="typ">TeamID</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="pun">小组</span><span class="pln">ID</span><span class="pun">,</span><span class="typ">PName</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="pun">姓名,</span><span class="typ">CYear</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="pun">年份,----</span><span class="pln">min</span><span class="pun">(</span><span class="typ">CMonth</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="pun">月份,</span><span class="pln">SUM</span><span class="pun">(</span><span class="typ">CMoney</span><span class="pun">)</span><span class="pln"> </span><span class="pun">总收入</span><span class="pln">FROM tb_Income</span><span class="typ">Where</span><span class="pln"> </span><span class="typ">CMonth</span><span class="pun">=</span><span class="lit">2</span><span class="pln">GROUP BY grouping SETS </span><span class="pun">((</span><span class="typ">TeamID</span><span class="pun">),(</span><span class="typ">TeamID</span><span class="pun">,</span><span class="typ">PName</span><span class="pun">),(</span><span class="typ">CYear</span><span class="pun">,</span><span class="typ">PName</span><span class="pun">))</span> |
四、使用Grouping标识汇总行（http://technet.microsoft.com/zh-cn/library/ms178544.aspx）
细心的朋友可能会注意到，如果Cube后有两个以上的汇总列时，可能会有一些列是Null,那么这些Null值究竟本身就是Null,还是由于聚合产生的Null呢，此时，GroupingID函数大显身手的机会来了。
<span class="com">/*********使用Grouping标识汇总行***************/</span><span class="pln"></span><span class="com">/********* 3w@live.cn 邀月***************/</span><span class="pln">SELECT </span><span class="typ">TeamID</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="pun">小组</span><span class="pln">ID</span><span class="pun">,</span><span class="typ">CYear</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="pun">年份,</span><span class="pln">CASE WHEN grouping</span><span class="pun">(</span><span class="typ">TeamID</span><span class="pun">)=</span><span class="lit">0</span><span class="pln"> AND grouping</span><span class="pun">(</span><span class="typ">CYear</span><span class="pun">)=</span><span class="lit">1</span><span class="pln"> THEN </span><span class="str">'小组汇总'</span><span class="pln">WHEN grouping</span><span class="pun">(</span><span class="typ">TeamID</span><span class="pun">)=</span><span class="lit">1</span><span class="pln"> AND grouping</span><span class="pun">(</span><span class="typ">CYear</span><span class="pun">)=</span><span class="lit">0</span><span class="pln"> THEN </span><span class="str">'年份汇总'</span><span class="pln">WHEN grouping</span><span class="pun">(</span><span class="typ">TeamID</span><span class="pun">)=</span><span class="lit">1</span><span class="pln"> AND grouping</span><span class="pun">(</span><span class="typ">CYear</span><span class="pun">)=</span><span class="lit">1</span><span class="pln"> THEN </span><span class="str">'所有汇总'</span><span class="pln"></span><span class="kwd">else</span><span class="pln"> </span><span class="str">'正常行'</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="pun">行类别,</span><span class="pln">SUM</span><span class="pun">(</span><span class="typ">CMoney</span><span class="pun">)</span><span class="pln"> </span><span class="pun">总收入</span><span class="pln">FROM tb_IncomeGROUP BY CUBE </span><span class="pun">(</span><span class="typ">TeamID</span><span class="pun">,</span><span class="typ">CYear</span><span class="pun">)</span> |
结果：
其实，还有更复杂的Grouping_ID，不过，一般情况下用不到，需要的同学，请看MSDN:
http://technet.microsoft.com/zh-cn/library/bb510624.aspx
小结：带有Cube,Rollup,grouping Sets的Group By函数在统计与分析中有着广泛的应用，相信它的高效简捷，在特定的场合会令人你爱不释手！
