如果一个表结构如下:
YM(年月)
WEIGHT(重量)有两条数据:
201003,100
201004,200请问如何根据月份展开数据
比如201003代表2010年3月,共有31天
把第一条数据展开为31条(增加日期一列,其他项目保持)
4月有30天,所以展开为30条。结果如下:
201003,100,20100301
201003,100,20100302
...
201003,100,20100331
201004,100,20100401
201004,100,20100402
...
201004,100,20100430谢谢!
YM(年月)
WEIGHT(重量)有两条数据:
201003,100
201004,200请问如何根据月份展开数据
比如201003代表2010年3月,共有31天
把第一条数据展开为31条(增加日期一列,其他项目保持)
4月有30天,所以展开为30条。结果如下:
201003,100,20100301
201003,100,20100302
...
201003,100,20100331
201004,100,20100401
201004,100,20100402
...
201004,100,20100430谢谢!
with tmp as(
select rownum rn from dual connect by rownum<32)
select t.*,t.ym||to_char(tmp.rn,'fm00') dt
from table1 t,tmp
where extract(day from last_day(to_date(t.ym,'yyyymm')))>=tmp.rn
order by dt
select level lv from dual connect by level<32
)
select trunc(to_date(ym,'yyyymm'))+ lv -1,weight from temp,tt
where extract(day from last_day(to_date(ym,'yyyymm')))>=temp.lv
order by trunc(to_date(ym,'yyyymm'))+ lv -1
select level the_day from dual connect by level <32
)
select ym,weight,to_char(to_date(ym,'yyyymm')+ the_day -1,'yyyymmdd') ymd
from tmp,atable
where extract(day from last_day(to_date(ym,'yyyymm'))) >= tmp.the_day
order by to_date(ym,'yyyymm')+ the_day -1