创建销售表：店铺销售表s1和电商销售表s2
create table s1 (id int, cp char(10), jg int, time date);
create table s2 (id int, cp char(10), jg int, time date);
commit;
表中添加数据
S1中数据
insert into s1 values (1,'shouji',2800,to_date('2012-12-12 12:12:12','yyyy-mm-dd hh24:mi:ss'));
insert into s1 values (2,'dianshi',15800,to_date('2012-12-10 12:12:12','yyyy-mm-dd hh24:mi:ss'));
insert into s1 values (3,'shouji',2800,to_date('2012-12-8 12:12:12','yyyy-mm-dd hh24:mi:ss'));
insert into s1 values (4,'bingxiang',8800,to_date('2012-11-12 12:12:12','yyyy-mm-dd hh24:mi:ss'));
insert into s1 values (5,'kongtiao',12800,to_date('2012-12-3 12:12:12','yyyy-mm-dd hh24:mi:ss'));
insert into s1 values (6,'dianshi',15800,to_date('2012-11-11 12:12:12','yyyy-mm-dd hh24:mi:ss'));
insert into s1 values (7,'shouji',2800,to_date('2012-11-10 12:12:12','yyyy-mm-dd hh24:mi:ss'));
insert into s1 values (8,'kongtiao',12800,to_date('2012-11-3 12:12:12','yyyy-mm-dd hh24:mi:ss'));
S2中数据
insert into s2 values (1,'shouji',2600,to_date('2012-12-12 12:12:12','yyyy-mm-dd hh24:mi:ss'));
insert into s2 values (2,'dianshi',15200,to_date('2012-12-10 12:12:12','yyyy-mm-dd hh24:mi:ss'));
insert into s2 values (3,'shouji',2600,to_date('2012-12-8 12:12:12','yyyy-mm-dd hh24:mi:ss'));
insert into s2 values (4,'bingxiang',8000,to_date('2012-11-12 12:12:12','yyyy-mm-dd hh24:mi:ss'));
insert into s2 values (6,'dianshi',15200,to_date('2012-11-11 12:12:12','yyyy-mm-dd hh24:mi:ss'));
insert into s2 values (7,'shouji',2600,to_date('2012-11-10 12:12:12','yyyy-mm-dd hh24:mi:ss'));
commit;
查看表中数据
select * from s1;
select * from s2;
分别统计数据表s1、s2 11月和12月份的销售额
select sum(jg) as heji12s1 from s1 where substr(to_char(time,'yyyy-mm-dd'),1,7)='2012-12' ;
select sum(jg) as heji12s2 from s2 where substr(to_char(time,'yyyy-mm-dd'),1,7)='2012-12';
select sum(jg) as heji11s1 from s1 where substr(to_char(time,'yyyy-mm-dd'),1,7)='2012-11';
select sum(jg) as heji11s2 from s2 where substr(to_char(time,'yyyy-mm-dd'),1,7)='2012-11';
11月和12月销售额分别求和
select (select sum(jg) as heji12s1 from s1 where substr(to_char(time,'yyyy-mm-dd'),1,7)='2012-12')
+(select sum(jg) as heji12s2 from s2 where substr(to_char(time,'yyyy-mm-dd'),1,7)='2012-12') as heji12
from dual;
select (select sum(jg) as heji11s1 from s1 where substr(to_char(time,'yyyy-mm-dd'),1,7)='2012-11')
+(select sum(jg) as heji11s2 from s2 where substr(to_char(time,'yyyy-mm-dd'),1,7)='2012-11')
as heji11 from dual;
对比12月和11月销售额
select ((select sum(jg) as heji12s1 from s1 where substr(to_char(time,'yyyy-mm-dd'),1,7)='2012-12')
+(select sum(jg) as heji12s2 from s2 where substr(to_char(time,'yyyy-mm-dd'),1,7)='2012-12') )
- ((select sum(jg) as heji11s1 from s1 where substr(to_char(time,'yyyy-mm-dd'),1,7)='2012-11')
+(select sum(jg) as heji11s2 from s2 where substr(to_char(time,'yyyy-mm-dd'),1,7)='2012-11'))
as xiaoshoucha from dual;
总结：
11月份销售总额为：66000元
12月份销售总额为：54600元
12月份销售量有所下降，与11月份对比，销售额下降了11400元。
