Help me !!!
小弟我写了这个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 一月,二月...
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
[解决办法]
这样也可以
select 一月,二月...
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 sales
Group By substr(Month,1,4)
)t
[解决办法]
select
一月,
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 sales
Group By substr(Month,1,4)
)t