如果一个表结构如下:
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谢谢!
解决方案 »
- linux下 sqlldr导入问题
- sqlserver 导入oracle出错
- 请问哪有数据库的例子能导入到我本机上做实验的
- oracle中的完全恢复和不完全恢复有什么区别
- 请问我如何把表中所有记录,用一个列的值去更改另外一个列的值
- OracleRAC问题
- 请大侠帮忙呀,等!!!!
- 求助:有上百万条的数据需导出到另外的数据库里.怎样做最好?
- 请问,Jdbc连接oracle数据库,我怎么在程序中获取oracle的错误码?
- 执行过程失败,原因为ora-01652和ORA-02063
- 满分相送,关于scheduler 创建的job不能自动运行的问题
- Oracle监听问题(OracleOraDb10g_home1TNSListener)帮忙解决一下。
select start_date + rownum - 1 the_day
from (
select to_date('201004' || '01', 'yyyymmdd') start_date,
add_months(to_date('201004' || '01', 'yyyymmdd'), 1) - 1 end_date
from dual)
connect by (start_date + rownum - 1) <= end_date
-- 顶!------------------
create table tdate(
ym varchar2(10),
weight number(18,0)
);insert into tdate(ym,weight) values('201003',100);
insert into tdate(ym,weight) values('201004',200);
commit;set serveroutput on;
declare
v_ym varchar2(10);
v_weight number(18,0);
v_fromDate varchar2(10);
v_toDate varchar2(10);
cursor c_ur is select ym, weight from tdate;
begin
for r_ur in c_ur loop
v_ym := r_ur.ym;
v_weight := r_ur.weight; v_fromDate := to_char(to_date(v_ym||'01','YYYYMMDD'),'YYYY-MM-DD');
v_toDate := to_char(last_day(to_date(v_ym||'01','YYYYMMDD')),'YYYY-MM-DD'); while v_fromDate <= v_toDate loop
dbms_output.put_line(v_fromDate||' '||to_char(v_weight));
v_fromDate := to_char(to_date(v_fromDate,'YYYY-MM-DD')+1,'YYYY-MM-DD');
end loop;
end loop;
end;
/
2 select '201003' ym,100 weight from dual
3 union all
4 select '201004',200 from dual
5 )
6 ,
7 tmp as(
8 select rownum rn from dual connect by rownum<32
9 )
10 select t.*,t.ym||to_char(tmp.rn,'fm00') dt
11 from tab t,tmp
12 where extract(day from last_day(to_date(t.ym,'yyyymm')))>=tmp.rn
13 order by dt
14 ;YM WEIGHT DT
------ ---------- ---------
201003 100 20100301
201003 100 20100302
201003 100 20100303
201003 100 20100304
201003 100 20100305
201003 100 20100306
201003 100 20100307
201003 100 20100308
201003 100 20100309
201003 100 20100310
201003 100 20100311
201003 100 20100312
201003 100 20100313
201003 100 20100314
201003 100 20100315
201003 100 20100316
201003 100 20100317
201003 100 20100318
201003 100 20100319
201003 100 20100320YM WEIGHT DT
------ ---------- ---------
201003 100 20100321
201003 100 20100322
201003 100 20100323
201003 100 20100324
201003 100 20100325
201003 100 20100326
201003 100 20100327
201003 100 20100328
201003 100 20100329
201003 100 20100330
201003 100 20100331
201004 200 20100401
201004 200 20100402
201004 200 20100403
201004 200 20100404
201004 200 20100405
201004 200 20100406
201004 200 20100407
201004 200 20100408
201004 200 20100409
201004 200 20100410YM WEIGHT DT
------ ---------- ---------
201004 200 20100411
201004 200 20100412
201004 200 20100413
201004 200 20100414
201004 200 20100415
201004 200 20100416
201004 200 20100417
201004 200 20100418
201004 200 20100419
201004 200 20100420
201004 200 20100421
201004 200 20100422
201004 200 20100423
201004 200 20100424
201004 200 20100425
201004 200 20100426
201004 200 20100427
201004 200 20100428
201004 200 20100429
201004 200 2010043061 rows selectedSQL
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
from (
select trunc(sysdate, 'mm') start_date from dual
)
connect by level <= 30;START_DATE+ROWNUM-1
-------------------
2010-6-1
2010-6-2
2010-6-3
2010-6-4
2010-6-5
2010-6-6
2010-6-7
2010-6-8
2010-6-9
2010-6-10
2010-6-11
2010-6-12
2010-6-13
2010-6-14
2010-6-15
2010-6-16
2010-6-17
2010-6-18
2010-6-19
2010-6-20
2010-6-21
2010-6-22
2010-6-23
2010-6-24
2010-6-25
2010-6-26
2010-6-27
2010-6-28
2010-6-29
2010-6-30
30 rows selected
select start_date + rownum - 1
from (
select trunc(sysdate, 'mm') start_date from dual
)
connect by level <= (trunc(last_day(sysdate), 'dd')) - trunc(sysdate, 'mm') + 1;
create table tbl(ym varchar2(10),weight int);
insert into tbl select 201003,100 from dual;
insert into tbl select 201004,200 from dual;
commit;select ym,weight,to_date(ym||'01','YYYY-MM-DD')+b.id from tbl a,
(select rownum-1 as id from dual
connect by rownum<=31) b
where to_char(to_date(ym||'01','YYYY-MM-DD')+b.id,'YYYYMM')=ym
order by a.ym ,b.id