sql中groupby的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 |