SQL Server2008 程序设计 汇总 group by ,WITH ROLLUP , WITH CUBE,Grouping sets(..)
来源:互联网 发布:淘宝rouwang家的模特 编辑:程序博客网 时间:2024/05/17 06:22
--SQL Server2008 程序设计 汇总 group by ,WITH ROLLUP WITH CUBE /******************************************************************************** *主题:SQL Server2008 程序设计 汇总 group by ,WITH ROLLUP WITH CUBE *说明:本文是个人学习的一些笔记和个人愚见 * 有很多地方你可能觉得有异议,欢迎一起讨论 *作者:Stephenzhou(阿蒙) *日期: 2012.12.5 *Mail:szstephenzhou@163.com *另外:转载请著名出处。 **********************************************************************************/
上测试数据
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:szstephenzhou@163.com
*另外:转载请著名出处。
*博客地址:http://blog.csdn.net/szstephenzhou
- SQL Server2008 程序设计 汇总 group by ,WITH ROLLUP , WITH CUBE,Grouping sets(..)
- group by(rollup ,cube ,grouping sets) and grouping
- GROUP BY中ROLLUP/CUBE/GROUPING/GROUPING SETS使用示例
- GROUP BY中ROLLUP/CUBE/GROUPING/GROUPING SETS使用示例
- oracle group by中rollup、cube、grouping sets、grouping
- GROUP BY中ROLLUP/CUBE/GROUPING/GROUPING SETS使用示例
- Hive中with cube、with rollup、grouping sets用法
- Oracle数据分组:group by,having,rollup,cube,grouping sets
- SQL Server 之 GROUP BY、GROUPING SETS、ROLLUP、CUBE (转)
- sql中 with rollup 、with cube、grouping 统计函数用法
- sql中 with rollup 、with cube、grouping 统计函数用法
- sql中 with rollup 、with cube、grouping 统计函数用法
- sql中 with rollup 、with cube、grouping 统计函数用法
- SQL Server关于WITH CUBE、WITH ROLLUP和GROUPING使用
- group by的rollup、cube、grouping、grouping sets 的用法浅析
- GROUPING SETS、ROLLUP、CUBE
- GROUPING SETS、ROLLUP、CUBE
- GROUPING SETS、ROLLUP、CUBE
- LeetCode: Plus One
- linux 文件系统的重新调整
- JEECMS2012二次开发文档
- java版客户端与服务器的socket通信以及上传下载
- Tomcat:IOException while loading persisted sessions: java.io.EOFException解决手记
- SQL Server2008 程序设计 汇总 group by ,WITH ROLLUP , WITH CUBE,Grouping sets(..)
- C++内存分配方式 与 Windows内存分配方式
- Linux系统的虚拟机分配IP操作
- SQLite3 API编程手册
- 初学者应该了解的编程陷阱:javascript篇
- 待续了解
- 浅学设计模式之简单工厂模式、工厂模式、抽象工厂模式
- 扩容磁盘空间
- 南瑞继保、深圳南瑞、南瑞集团、国电南自的区别!