表有两个字段
字段1(VARCHAR2):存储月份 字段2(NUMBER):存储值字段1 字段2
201010 4
201102 7我想补齐不连续的月,谢谢,结果如下:
字段1 字段2
201010 4
201011 0
201012 0
201101 0
201102 7
字段1(VARCHAR2):存储月份 字段2(NUMBER):存储值字段1 字段2
201010 4
201102 7我想补齐不连续的月,谢谢,结果如下:
字段1 字段2
201010 4
201011 0
201012 0
201101 0
201102 7
解决方案 »
- 问两个小问题~真的很小哦
- 第一天学习Oracle 有些问题问一下
- 怎么审计oracle的执行计划啊
- oracle 服务意外停止
- 求SQL查询大数据优化
- 那位高人知道ORACLE EBS11 财务总帐分录表是那几个?
- Oracle Forms Builder 6i 不能用CASE语句?
- 在oracle中怎么将200501,200504,200502,200510重新排序为200501,200502,200504,200510
- 初学oracle 备份出了这样的问题......
- ORA-01219: database not open: queries allowed on fixed tables/views only
- sqlldr 插入到多表的问题
- 看似简单,想破头皮,毫无头绪
-- 要生成连续月份很简单:用递归查询 connect by
with t as
(
select '201010' as smonth, 4 as val from dual
union all
select '201102' as smonth, 7 as val from dual
union all
select '201108' as smonth, 10 as val from dual
)
select a.smonth, 0 as val
from (select to_char(add_months(to_date(smin,'yyyymm'), rownum),'yyyymm') as smonth
from (select min(smonth) as smin,
max(smonth) as smax
from t) t1
connect by rownum < months_between(to_date(t1.smax, 'yyyymm'), to_date(t1.smin, 'yyyymm'))) a
where smonth not in(select smonth from t)
order by smonth;
表
年月 数值
201010 4
201102 7按月环比结果如下:环比增长=(本月数值-上月数值)/本月数值
年月 数值 环比增长
201010 4 0
201011 0 0
201012 0 0
201101 0 0
201102 7 100
注:年月为字符
求SQL,谢谢!
with t as(
select '201010' as smonth, 4 as val from dual
union all
select '201102' as smonth, 7 as val from dual
union all
select '201108' as smonth, 10 as val from dual
)
select mon 年月,
val 数值,
Decode(val,0,0,(val-lag_val)/val)*100 环比增长
from (
select a.mon,nvl(t.val,0) val,Lag(Nvl(t.val,0),1,Nvl(t.val,0))over(order by a.mon) lag_val from(
select to_char(add_months(m1,level-1),'yyyymm') mon from (
select min(to_date(smonth,'yyyymm')) m1,max(to_date(smonth,'yyyymm')) m2 from t
)
connect by level<=months_between(m2,m1)+1
) a
left join t on a.mon=t.smonth(+)
order by 1
)年月 数值 环比增长
201010 4 0
201011 0 0
201012 0 0
201101 0 0
201102 7 100
201103 0 0
201104 0 0
201105 0 0
201106 0 0
201107 0 0
201108 10 100