GROUP BY, CUBE, ROLLUP and SQL SERVER 2005
来源:互联网 发布:数据库自学教程 编辑:程序博客网 时间:2024/06/08 10:28
I learn about CUBE and ROLLUP function today and I found it really usefull for generating reports. Let me give you one example where we can use ROLLUP and CUBE. Lets say you are developing the E-Commerce application and administrator wants the report which shows products purchased by all user group by product and buyer. You will say that’s really easy and can write the query as shown below,
1: SELECT CustomerName,CustomerName,SUM(Quantity*PricePerItem)
2: FROM Orders GROUP BY CustomerName,CustomerName
Fig - (1) Group By clause.
Which will returns the result as shown below,
Fig - (2) Result of GROUP BY clause
However what if you want result as display below,
Fig - (3) Desire result
Here ROLLUP and CUBE comes into the picture and help us. The above result is generated using ROLLUP. ROLLUP adds new row for each column used in GROUP BY clause. First have a look at the query and then we will discuss more,
1: SELECT
2: CASE
3: WHEN GROUPING(customername) = 1 THEN 'All Customer'
4: ELSE customername END CustomerName,
5: CASE WHEN GROUPING(itemname) = 1 THEN 'All Items'
6: ELSE itemname END ItemName,
7: SUM(Quantity*PricePerCase)
8: FROM orders GROUP BY customername,itemname
9: WITH ROLLUP
Fig - (4) Query for output shown in fig - 3
As you can see in query we have used ROLLUP after GROUP BY clause. Here ROLLUP has added new row at line 3,5 and 6 in fig 3. If you have used only one columns in GROUP BY clause then only row will have been added. The new clause in query is GROUPING. GROUPING clause add new column in result set. The value in new column can either be 0 or 1. If the new row is added by the ROLLUP or CUBE then the value of GROUPING column is 1 else 0.
In fig - 3 we have total price by user name, wow lets assume you want the total price by item name also. Here you have to use CUBE as shown below,
1: SELECT
2: CASE
3: WHEN GROUPING(customername) = 1 THEN 'All Customer'
4: ELSE customername END CustomerName,
5: CASE WHEN GROUPING(itemname) = 1 THEN 'All Items'
6: ELSE itemname END ItemName,
7: SUM(Quantity*PricePerCase)
8: FROM orders GROUP BY customername,itemname
9: WITH CUBE
Fig - (5) CUBE clause
Fig - (6) Result of CUBE
- GROUP BY, CUBE, ROLLUP and SQL SERVER 2005
- group by cube & rollup
- SQL Server 之 GROUP BY、GROUPING SETS、ROLLUP、CUBE (转)
- group by(rollup ,cube ,grouping sets) and grouping
- sql rollup and cube
- oracle group by 中rollup和cube
- GROUP BY 、CUBE 和 ROLLUP 用法对比
- oracle group by 中rollup和cube
- mssql分组 ( ROLLUP /CUBE/GROUP BY /)
- oracle 中group by cube和rollup
- ROLLUP、CUBE、GROUP BY的使用区别
- Oracle中的分组group by rollup/cube
- Group by RollUp&Group by CUBE&GroupBy 区别
- Group by RollUp&Group by CUBE&GroupBy 区别
- Oracle group by 基本及的拓展 ROLLUP, CUBE, GROUPING 功能and GROUPING 集合
- Oracle group by 基本及的拓展 ROLLUP, CUBE, GROUPING 功能and GROUPING 集合
- SQL Server2008 程序设计 汇总 group by ,WITH ROLLUP , WITH CUBE,Grouping sets(..)
- Oracle的 GROUP BY 的 ROLLUP和CUBE 语句(转)
- 常用字符,点,叉、、、
- listctrl用法
- 从Yelp看第三方公共形象评价和管理机制
- 泛型的注意点
- wxWidgets 按钮文字换行
- GROUP BY, CUBE, ROLLUP and SQL SERVER 2005
- struts2中iterator标签的相关使用
- error C2065: ''W2T'' : undeclared identifie
- mysql -- mysqldump错误:Got error: 1044: Access denied for user ... when using LOCK TABLES
- CSS书写规范及方法
- glscene学习(一)
- 内存句柄HGLOBAL
- 查询历史销售记录
- 两个VOIP相关问题