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