当前位置: 代码迷 >> SQL >> SQL Server2008 程序设计 集锦 group by ,WITH ROLLUP , WITH CUBE,Grouping sets(.)
  详细解决方案

SQL Server2008 程序设计 集锦 group by ,WITH ROLLUP , WITH CUBE,Grouping sets(.)

热度:673   发布时间:2016-05-05 12:52:25.0
SQL Server2008 程序设计 汇总 group by ,WITH ROLLUP , WITH CUBE,Grouping sets(..)
--SQL Server2008 程序设计 汇总 group by ,WITH ROLLUP  WITH CUBE         /********************************************************************************           *主题:SQL Server2008 程序设计 汇总 group by ,WITH ROLLUP  WITH CUBE    *说明:本文是个人学习的一些笔记和个人愚见           *      有很多地方你可能觉得有异议,欢迎一起讨论                     *作者:Stephenzhou(阿蒙)           *日期: 2012.12.5         *Mail:[email protected]           *另外:转载请著名出处。          **********************************************************************************/    


上测试数据

 

 

IF OBJECT_ID('Inventory') is not nulldrop table Inventorygocreate table Inventory(Store varchar(2),Item varchar(20),Color varchar(10),Quantity decimal )insert into Inventory values('NY','Table','Blue',124)insert into Inventory values('NJ','Table','Blue',100)insert into Inventory values('NY','Table','Red',29)insert into Inventory values('NJ','Table','Red',56)insert into Inventory values('PA','Table','Red',138)insert into Inventory values('NY','Table','Green',229)insert into Inventory values('PA','Table','Green',304)insert into Inventory values('NY','Chair','Blue',101)insert into Inventory values('NJ','Chair','Blue',22)insert into Inventory values('NY','Chair','Red',21)insert into Inventory values('NJ','Chair','Red',10)insert into Inventory values('PA','Chair','Red',136)insert into Inventory values('NJ','Sofa','Green',2)


  --一般的group by

 

select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores from Inventory group by  Item,Color order by Item,Color/*Item                 Color      TotalQuantity                           Stores-------------------- ---------- --------------------------------------- -----------Chair                Blue       123                                     2Chair                Red        167                                     3Sofa                 Green      2                                       1Table                Blue       224                                     2Table                Green      533                                     2Table                Red        223                                     3(6 行受影响)*/  


 

 GROUP BY   .. WITH ROLLUP

多了4个rollup行

 

 select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores from Inventory group by  Item,Color WITH ROLLUP --group by rollup(item,color)order by Item,Color/*Item                 Color      TotalQuantity                           Stores-------------------- ---------- --------------------------------------- -----------NULL                 NULL       1272                                    13Chair                NULL       290                                     5Chair                Blue       123                                     2Chair                Red        167                                     3Sofa                 NULL       2                                       1Sofa                 Green      2                                       1Table                NULL       980                                     7Table                Blue       224                                     2Table                Green      533                                     2Table                Red        223                                     3(10 行受影响)*/


 

with cube 多维数据集,多维数据集的纬度取决于分组列的数目

 

 select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores from Inventory group by  Item,Color WITH cube --group by cube(item,color)order by Item,Color/*Item                 Color      TotalQuantity                           Stores-------------------- ---------- --------------------------------------- -----------NULL                 NULL       1272                                    13NULL                 Blue       347                                     4NULL                 Green      535                                     3NULL                 Red        390                                     6Chair                NULL       290                                     5Chair                Blue       123                                     2Chair                Red        167                                     3Sofa                 NULL       2                                       1Sofa                 Green      2                                       1Table                NULL       980                                     7Table                Blue       224                                     2Table                Green      533                                     2Table                Red        223                                     3(13 行受影响)*/


 

 

仅返回最高级别

 select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores from Inventory group by GROUPING sets(Item,Color)order by Item,Color/*Item                 Color      TotalQuantity                           Stores-------------------- ---------- --------------------------------------- -----------NULL                 Blue       347                                     4NULL                 Green      535                                     3NULL                 Red        390                                     6Chair                NULL       290                                     5Sofa                 NULL       2                                       1Table                NULL       980                                     7(6 行受影响)*/ 


 

混合使用:

返回store最高级别和cube的两个item,color纬度所以级别组合

    select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores from Inventory group by GROUPING sets(Store) ,cube(Item,color)order by Item,Color/*Item                 Color      TotalQuantity                           Stores-------------------- ---------- --------------------------------------- -----------NULL                 NULL       190                                     5NULL                 NULL       504                                     5NULL                 NULL       578                                     3NULL                 Blue       225                                     2NULL                 Blue       122                                     2NULL                 Green      2                                       1NULL                 Green      229                                     1NULL                 Green      304                                     1NULL                 Red        274                                     2NULL                 Red        66                                      2NULL                 Red        50                                      2Chair                NULL       32                                      2Chair                NULL       122                                     2Chair                NULL       136                                     1Chair                Blue       101                                     1Chair                Blue       22                                      1Chair                Red        10                                      1Chair                Red        21                                      1Chair                Red        136                                     1Sofa                 NULL       2                                       1Sofa                 Green      2                                       1Table                NULL       156                                     2Table                NULL       382                                     3Table                NULL       442                                     2Table                Blue       100                                     1Table                Blue       124                                     1Table                Green      229                                     1Table                Green      304                                     1Table                Red        29                                      1Table                Red        56                                      1Table                Red        138                                     1(31 行受影响)*/


 

 

 

 *作者:Stephenzhou(阿蒙)     
 *日期: 2012.12.5
 *Mail:[email protected]     
 *另外:转载请著名出处。
 *博客地址:http://blog.csdn.net/szstephenzhou

 

 

 

 

 

 

  相关解决方案