我想对数据进行统计,比如对2007年的数据进行统计,但是4月、11月与12月的数据还没有,但又要通过数据库sql取出个-表示要如何处理
最后想要的结果如下:
1月 2月 3月 4月 5月 ... 10月 11月 12月
出售件数 3 5 1 - 8 4 - -
出售总价格 1500 2500 500 - 4000 ... 2000 - -
请帮忙看看要如何才能得出这样的结果?
最后想要的结果如下:
1月 2月 3月 4月 5月 ... 10月 11月 12月
出售件数 3 5 1 - 8 4 - -
出售总价格 1500 2500 500 - 4000 ... 2000 - -
请帮忙看看要如何才能得出这样的结果?
from table
id integer
createdate date
sell integer
2楼回复的不太对,因为4月、11月与12月里就没有数据,所以sql查询里就不会查出来,nvl好象就没用了
(select rownum x from TABLE_NAME where rownum<=12) a
left join
(select ltrim(to_char(createdate,'MM'),0) x,count(*) as y,500*count(*) z
From TABLE_NAMEwhere to_char(createdate,'YYYY') ='2007'
group by to_char(createdate,'MM') )b
on a.x=b.x
order by a.x;
create table t
(bm varchar(20),
yf char(2),
zc varchar(20),
je NUMBER);
insert into t
(select '部门A',1,'内部',10 from DUAL)
union all
(select '部门A',1,'外部',15 from DUAL)
union all
(select '部门A',2,'内部',1 from DUAL)
union all
(select '部门B',1,'内部',10 from DUAL)
union all
(select '部门B',2,'内部',10 from DUAL)
union all
(select '部门B',3,'内部',10 from DUAL);select nvl(bm,'合计'),nvl(zc,'小计'),
sum(decode(yf,1,je,0)) as "1月",
sum(decode(yf,2,je,0)) as "2月",
sum(decode(yf,3,je,0)) as "3月",
sum(decode(yf,4,je,0)) as "4月",
sum(decode(yf,5,je,0)) as "5月",
sum(decode(yf,6,je,0)) as "6月",
sum(decode(yf,7,je,0)) as "7月",
sum(decode(yf,8,je,0)) as "8月",
sum(decode(yf,9,je,0)) as "9月",
sum(decode(yf,10,je,0)) as "10月",
sum(decode(yf,11,je,0)) as "11月",
sum(decode(yf,12,je,0)) as "12月",
sum(je) as "合计"
from t
group by rollup(bm,zc);
NVL(BM,'合计') NVL(ZC,'小计') 1月 2月 3月
---------------------------------------------------------------------------
部门A 内部 10 1 0
部门A 外部 15 0 0
部门A 小计 25 1 0
部门B 内部 10 10 10
部门B 小计 10 10 10
合计 小计 35 11 10