ABC公司销售产品表test中现有数据如下,现今需要进行财务汇总而生成一个报表,
见下图,请您帮他们写一个SQL查询实现些报表.(年份排序)
create table test(SID NUMBER, SDATE DATE, NUM NUMBER, /
NUMBER(7,2));
insert into test values(1000,DATE'2006-10-02', 10, 50);
insert into test values(1001,DATE'2006-11-12', 3, 150);
insert into test values(1002,DATE'2006-09-23', 8, 42);
insert into test values(1001,DATE'2006-02-11', 3, 50);
insert into test values(1003,DATE'2007-07-02', 100, 10);
insert into test values(1004,DATE'2007-02-25', 20, 500);
===========================================
1 1000 2006-10-02 10 50.00
2 1001 2006-11-12 3 150.00
3 1002 2006-09-23 8 42.00
4 1003 2007-07-02 100 10.00
5 1004 2007-02-25 20 500.00
6 1001 2006-02-11 3 50.00
报表图:
年份 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 总计
2006 = 150 = = = = = = 336 500 450 = $1436
2007 = 10000 = = = = 1000 = = = = = $11000
见下图,请您帮他们写一个SQL查询实现些报表.(年份排序)
create table test(SID NUMBER, SDATE DATE, NUM NUMBER, /
NUMBER(7,2));
insert into test values(1000,DATE'2006-10-02', 10, 50);
insert into test values(1001,DATE'2006-11-12', 3, 150);
insert into test values(1002,DATE'2006-09-23', 8, 42);
insert into test values(1001,DATE'2006-02-11', 3, 50);
insert into test values(1003,DATE'2007-07-02', 100, 10);
insert into test values(1004,DATE'2007-02-25', 20, 500);
===========================================
1 1000 2006-10-02 10 50.00
2 1001 2006-11-12 3 150.00
3 1002 2006-09-23 8 42.00
4 1003 2007-07-02 100 10.00
5 1004 2007-02-25 20 500.00
6 1001 2006-02-11 3 50.00
报表图:
年份 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 总计
2006 = 150 = = = = = = 336 500 450 = $1436
2007 = 10000 = = = = 1000 = = = = = $11000
from test
Group by to_char(SDATE,'YYYY')
还得高手写个好SQL
sum(decode(smonth,'01',je,null)) 一月,
sum(decode(smonth,'02',je,null)) 二月,
sum(decode(smonth,'03',je,null)) 三月,
sum(decode(smonth,'04',je,null)) 四月,
sum(decode(smonth,'05',je,null)) 五月,
sum(decode(smonth,'06',je,null)) 六月,
sum(decode(smonth,'07',je,null)) 七月,
sum(decode(smonth,'08',je,null)) 八月,
sum(decode(smonth,'09',je,null)) 九月,
sum(decode(smonth,'10',je,null)) 十月,
sum(decode(smonth,'11',je,null)) 十一月,
sum(decode(smonth,'12',je,null)) 十二月,
'$'||sum(je) 总计
from (select sid,substr(to_char(sdate,'yyyymmdd'),1,4) syear,substr(to_char(sdate,'yyyymmdd'),5,2) smonth,num* bs je from test)
group by syear