小弟我写了这个SQL语句来统计一年的销售记录
Select substr(Month,1,4),
Sum(decode(substr(Month,5,2),'01',sale,0)) As 一月,
Sum(decode(substr(Month,5,2),'02',sale,0))As 二月,
Sum(decode(substr(Month,5,2),'03',sale,0))As 三月,
Sum(decode(substr(Month,5,2),'04',sale,0))As 四月,
Sum(decode(substr(Month,5,2),'05',sale,0))As 五月,
Sum(decode(substr(Month,5,2),'06',sale,0))As 六月,Sum(decode(substr(Month,5,2),'07',sale,0))As 七月,
Sum(decode(substr(Month,5,2),'08',sale,0))As 八月,
Sum(decode(substr(Month,5,2),'09',sale,0))As 九月,Sum(decode(substr(Month,5,2),'10',sale,0))As 十月,
Sum(decode(substr(Month,5,2),'11',sale,0))As 十一月,Sum(decode(substr(Month,5,2),'12',sale,0))As 十二月
From sales
Group By substr(Month,1,4)
现在的情况是
请问,如果我6月份的销售为0,那就是用5月的销售数量来代替,这样如何去写
在线等啊!!!!!!
Select substr(Month,1,4),
Sum(decode(substr(Month,5,2),'01',sale,0)) As 一月,
Sum(decode(substr(Month,5,2),'02',sale,0))As 二月,
Sum(decode(substr(Month,5,2),'03',sale,0))As 三月,
Sum(decode(substr(Month,5,2),'04',sale,0))As 四月,
Sum(decode(substr(Month,5,2),'05',sale,0))As 五月,
Sum(decode(substr(Month,5,2),'06',sale,0))As 六月,Sum(decode(substr(Month,5,2),'07',sale,0))As 七月,
Sum(decode(substr(Month,5,2),'08',sale,0))As 八月,
Sum(decode(substr(Month,5,2),'09',sale,0))As 九月,Sum(decode(substr(Month,5,2),'10',sale,0))As 十月,
Sum(decode(substr(Month,5,2),'11',sale,0))As 十一月,Sum(decode(substr(Month,5,2),'12',sale,0))As 十二月
From sales
Group By substr(Month,1,4)
现在的情况是
请问,如果我6月份的销售为0,那就是用5月的销售数量来代替,这样如何去写
在线等啊!!!!!!
解决方案 »
- oracle11g冷备恢复,重建控制文件后报错
- rman登录提示权限不足
- 一个删除ORACLE用户的SHELL执行不了,大家帮忙看看
- oracle9i的一个session要占多少内存,怎么我的HP下的oracle9i经常出现内存不够的现象,来者有分。
- sql 子查询 用oracle函数实现,怎么做?
- 相当简单的问题
- Oracle数据库。。从一台机子导到另外一台机子,我现在马上要去客户那里解决这个问题。求高手指点
- oracle10g这么多版本,应该安装那个?
- 对n张表建立视图速度是否会很慢?
- 有没有办法从.ora文件中恢复数据
- 求PROCEDURE中SELECT.....INTO ARRAY 的具体用法
- 大型网站的数据库配置是怎样的?
Sum(decode(substr(Month,5,2),'06',sale,0))As 六月)但是这样你12个月的结果,要套11个nvl,你慢慢套吧!
希望有更好的解决方案!
decode(五月,0,六月) as 五月,...,十二月
from
(
Select substr(Month,1,4),
Sum(decode(substr(Month,5,2),'01',sale,0)) As 一月,
Sum(decode(substr(Month,5,2),'02',sale,0))As 二月,
Sum(decode(substr(Month,5,2),'03',sale,0))As 三月,
Sum(decode(substr(Month,5,2),'04',sale,0))As 四月,
Sum(decode(substr(Month,5,2),'05',sale,0))As 五月,
Sum(decode(substr(Month,5,2),'06',sale,0))As 六月,Sum(decode(substr(Month,5,2),'07',sale,0))As 七月,
Sum(decode(substr(Month,5,2),'08',sale,0))As 八月,
Sum(decode(substr(Month,5,2),'09',sale,0))As 九月,Sum(decode(substr(Month,5,2),'10',sale,0))As 十月,
Sum(decode(substr(Month,5,2),'11',sale,0))As 十一月,Sum(decode(substr(Month,5,2),'12',sale,0))As 十二月
From sales
Group By substr(Month,1,4)
)t
case when 五月=0 then 六月 else 五月 end 五月,...,十二月
from
(
Select substr(Month,1,4),
Sum(decode(substr(Month,5,2),'01',sale,0)) As 一月,
Sum(decode(substr(Month,5,2),'02',sale,0))As 二月,
Sum(decode(substr(Month,5,2),'03',sale,0))As 三月,
Sum(decode(substr(Month,5,2),'04',sale,0))As 四月,
Sum(decode(substr(Month,5,2),'05',sale,0))As 五月,
Sum(decode(substr(Month,5,2),'06',sale,0))As 六月,Sum(decode(substr(Month,5,2),'07',sale,0))As 七月,
Sum(decode(substr(Month,5,2),'08',sale,0))As 八月,
Sum(decode(substr(Month,5,2),'09',sale,0))As 九月,Sum(decode(substr(Month,5,2),'10',sale,0))As 十月,
Sum(decode(substr(Month,5,2),'11',sale,0))As 十一月,Sum(decode(substr(Month,5,2),'12',sale,0))As 十二月
From salesGroup By substr(Month,1,4)
)t
一月,
case when 二月2=0 then
case when 一月<>0 then
一月
else 0
end
else 一月
end 二月,
case when 三月=0 then
case when 二月<>0 then 二月
when 一月<>0 then 一月
else 0 end
else 一月
end 三月,
case when 四月月=0 then
case when 三月<>0 then 三月
when 二月<>0 then 二月
when 一月<>0 then 一月
else 0 end
else 一月
end 四月
.................
from
(
Select substr(Month,1,4),
Sum(decode(substr(Month,5,2),'01',sale,0)) As 一月,
Sum(decode(substr(Month,5,2),'02',sale,0))As 二月,
Sum(decode(substr(Month,5,2),'03',sale,0))As 三月,
Sum(decode(substr(Month,5,2),'04',sale,0))As 四月,
Sum(decode(substr(Month,5,2),'05',sale,0))As 五月,
Sum(decode(substr(Month,5,2),'06',sale,0))As 六月,Sum(decode(substr(Month,5,2),'07',sale,0))As 七月,
Sum(decode(substr(Month,5,2),'08',sale,0))As 八月,
Sum(decode(substr(Month,5,2),'09',sale,0))As 九月,Sum(decode(substr(Month,5,2),'10',sale,0))As 十月,
Sum(decode(substr(Month,5,2),'11',sale,0))As 十一月,Sum(decode(substr(Month,5,2),'12',sale,0))As 十二月
From salesGroup By substr(Month,1,4)
)t