sql中group by的rollup、Cube 、Sets 用法

Group by

 

基础数据

id	name	ptype	price	Regist_date
001	T恤	衣服	1000	2009-09-20
002	打孔器	办公用品	500	2009-09-11
003	运动T恤	衣服	4000
004	菜刀	厨房用品	3000	2009-09-20
005	高压锅	厨房用品	6800	2009-01-15
006	叉子	厨房用品	500	2009-09-20
007	擦菜板	厨房用品	880	2008-04-28
008	圆珠笔	办公用品	100	2009-11-11

 

 

select  ptype ,sum(price)  from product group by product_type;

执行结果为；

ptype  sum(price)

衣服    5000

办公用品 600

厨房用品 11180

 

得到合计行(rollup)

select  ptype  ,sum(price)  from product group by  rollup(product_type);

ptype   sum(price)

         16780

衣服     5000

办公用品 600

厨房用品 11180

 

Select ptype,regist_date,sum(price) as sum_price

From product

Group by rollup (ptype,regist_date)

执行结果：

ptype    regist_date     sum_price

 

  

ptype	regist_date	sum_price
		16780	合计
厨房用品		11180	厨房用品小计
厨房用品	2008-04-28	880
厨房用品	2009-01-15	6800
厨房用品	2009-09-20	3500
办公用品		600	办公用品小计
办公用品	2009-09-11	500
办公用品	2009-11-11	100
衣服		5000	--衣服小计
衣服	2009-09-20	1000
衣服		4000	日期为空，当做单独行

 

以下两个功能很有意思，用法和 rollup 一样

Cube --用数据来搭积木,根据聚合键的所有可能的组合计算

Sets ---取得期望的积木，取得期望的不固定的结果

 

Sql:

Select case when grouping(ptype) =1

Then ‘商品种类 合计’

Else ptype end as ptype,

      case when grouping(regist_date) =1

Then ‘登记日期 合计’

Else cast(regist_date as varchar(16)) end as regist_date,

      Sum(price) as sum_price

From  product

Group by cube(ptype,regist_date);

 

执行结果：

ptype	regist_date	sum_price
商品种类 合计	登记日期 合计	16780
商品种类 合计	2008-04-28	880
商品种类 合计	2009-01-15	6800
商品种类 合计	2009-09-11	500
商品种类 合计	2009-09-20	4500
商品种类 合计	2009-11-11	100
商品种类 合计		4000
厨房用品	登记日期 合计	11180
厨房用品	2008-04-28	880
厨房用品	2009-01-15	6800
厨房用品	2009-09-20	3500
办公用品	登记日期 合计	600
办公用品	2009-09-11	500
办公用品	2009-11-11	100
衣服	登记日期 合计	5000
衣服	2009-09-20	1000
衣服		4000

 

 

Sql:

Select case when grouping(ptype) =1

Then ‘商品种类 合计’

Else ptype end as ptype,

      case when grouping(regist_date) =1

Then ‘登记日期 合计’

Else cast(regist_date as varchar(16)) end as regist_date,

      Sum(price) as sum_price

From  product

Group by sets(ptype,regist_date);

ptype	regist_date	sum_price
商品种类 合计	2008-04-28	880
商品种类 合计	2009-01-15	6800
商品种类 合计	2009-09-11	500
商品种类 合计	2009-09-20	4500
商品种类 合计	2009-11-11	100
商品种类 合计		4000
厨房用品	登记日期 合计	11180
办公用品	登记日期 合计	600
办公用品	2009-09-11	500
办公用品	2009-11-11	100
衣服	登记日期 合计	5000