2013年（350）
分类： Mysql/postgreSQL
2013-04-25 10:32:25
一、按指定规则生成指定商品2002年销售额，其中彩电项值为2001年的销售额加2000年的销售额，微波炉为2000年销售额, 然后汇总彩电+微波炉的2002年总销售额。
表数据如下：
CITY PRODUCT YEAR SALES
------ -------------- -------- ---------
北京 彩电 1999 3000
北京 彩电 2000 2500
北京 彩电 2001 4500
北京 微波炉 1999 800
北京 微波炉 2000 7000
北京 微波炉 2001 333
北京 冰箱 1999 2323
北京 冰箱 2000 1212
北京 冰箱 2001 7676
天津 彩电 1999 212121
天津 彩电 2000 434343
天津 彩电 2001 564566
天津 微波炉 1999 23432
天津 微波炉 2000 232
天津 微波炉 2001 34234
天津 冰箱 1999 324324
天津 冰箱 2000 8987686
天津 冰箱 2001 768678
要求用实现如下效果：
CITY PRODUCT YEAR SALES
------ -------------- -------- ---------
天津 微波炉 1999 23432
天津 微波炉 2000 232
天津 微波炉 2001 34234
天津 微波炉 2002 232
天津 冰箱 1999 324324
天津 冰箱 2000 8987686
天津 冰箱 2001 768678
天津 彩电 1999 212121
天津 彩电 2000 434343
天津 彩电 2001 564566
天津 彩电 2002 998909
天津 彩电+微波炉 2002 999141
北京 微波炉 1999 800
北京 微波炉 2000 7000
北京 微波炉 2001 333
北京 微波炉 2002 7000
北京 冰箱 1999 2323
北京 冰箱 2000 1212
北京 冰箱 2001 7676
北京 彩电 1999 3000
北京 彩电 2000 2500
北京 彩电 2001 4500
北京 彩电 2002 7000
北京 彩电+微波炉 2002 14000
建表语句如下：
create table tmp1(CITY varchar2(20), PRODUCT varchar2(20), YEAR number, SALES number);
insert into tmp1 values ('北京','彩电', 1999, 3000);
insert into tmp1 values ('北京','彩电', 2000, 2500);
insert into tmp1 values ('北京','彩电', 2001, 4500);
insert into tmp1 values ('北京','微波炉', 1999, 800);
insert into tmp1 values ('北京','微波炉', 2000, 7000);
insert into tmp1 values ('北京','微波炉', 2001, 333);
insert into tmp1 values ('北京','冰箱', 1999, 2323);
insert into tmp1 values ('北京','冰箱', 2000, 1212);
insert into tmp1 values ('北京','冰箱', 2001, 7676);
insert into tmp1 values ('天津','彩电', 1999, 212121);
insert into tmp1 values ('天津','彩电', 2000, 434343);
insert into tmp1 values ('天津','彩电', 2001, 564566);
insert into tmp1 values ('天津','微波炉', 1999, 23432);
insert into tmp1 values ('天津','微波炉', 2000, 232);
insert into tmp1 values ('天津','微波炉', 2001, 34234);
insert into tmp1 values ('天津','冰箱', 1999, 324324);
insert into tmp1 values ('天津','冰箱', 2000, 8987686);
insert into tmp1 values ('天津','冰箱', 2001, 768678);
解题思路：
本题初看起来一般都会下意识选择通过group by rollup子句生成，但如果你选择直接通过group by rollup的方式：
JSSWEB> select city, product, year, sum(sales)
2 from tmp1
3 group by city, rollup(product, year)
4 ;
CITY PRODUCT YEAR SUM(SALES)
-------------------- -------------------- ---------- ----------
北京 冰箱 2000 1212
北京 冰箱 1999 2323
北京 冰箱 2001 7676
北京 冰箱 11211
北京 彩电 2000 2500
北京 彩电 1999 3000
北京 彩电 2001 4500
北京 彩电 10000
北京 微波炉 2000 7000
北京 微波炉 1999 800
北京 微波炉 2001 333
北京 微波炉 8133
北京 29344
天津 冰箱 2000 8987686
天津 冰箱 1999 324324
天津 冰箱 2001 768678
天津 冰箱 10080688
天津 彩电 2000 434343
天津 彩电 1999 212121
天津 彩电 2001 564566
天津 彩电 1211030
天津 微波炉 2000 232
天津 微波炉 1999 23432
天津 微波炉 2001 34234
天津 微波炉 57898
天津 11349616
26 rows selected
这跟预想的结果有不小差距。
这是因为要求的数据是涉及逻辑运算的，比如某些数据需要2001年相加，某些数据由2000+2001汇总，因此我们在group by之前需要先按照条件对sales做些处理，彩电要取2000年和2001年的，而微波炉要取2000年的。
JSSWEB> select city,
2 product,
3 year,
4 sales,
5 decode(product,
6 '彩电',
7 decode(year, 2000, sales, 2001, sales, 0),
8 '微波炉',
9 decode(year, 2000, sales, 0)) ns
10 from tmp1;
CITY PRODUCT YEAR SALES NS
-------------------- -------------------- ---------- ---------- ----------
北京 彩电 1999 3000 0
北京 彩电 2000 2500 2500
北京 彩电 2001 4500 4500
北京 微波炉 1999 800 0
北京 微波炉 2000 7000 7000
北京 微波炉 2001 333 0
北京 冰箱 1999 2323
北京 冰箱 2000 1212
北京 冰箱 2001 7676
天津 彩电 1999 212121 0
天津 彩电 2000 434343 434343
天津 彩电 2001 564566 564566
天津 微波炉 1999 23432 0
天津 微波炉 2000 232 232
天津 微波炉 2001 34234 0
天津 冰箱 1999 324324
天津 冰箱 2000 8987686
天津 冰箱 2001 768678
18 rows selected
然后在做sum的时候，我们只需要判断一下，比如产品为彩电或微波炉的话，sum(ns)，否则sum(sales)，最后按照城市产品和年份排下序即可：
*space单篇字数限制，请见下一篇~
http://space.itpub.net/?uid-7607759-action-viewspace-itemid-237855
