求一个SQL语句
表结构如下:—————————————————————————————————————————日期|数据—————————————————————————————————...
表结构如下:
—————————————————————————————————————————
日期 | 数据
—————————————————————————————————————————
2010-12-05 | 12.5*14.0*15.8*18.5
—————————————————————————————————————————
2010-12-06 | 14.3*15.5*17.5*19.0
—————————————————————————————————————————
。。。
日期对应的以*为分隔符的数据是每6小时一个时间点的数据,每天4个点,现在希望得到如下的结果集
时间 | 数据
——————————————————————
2010-12-05 00:00:00 | 12.5
——————————————————————
2010-15-05 06:00:00 | 14.0
——————————————————————
2010-15-05 12:00:00 | 15.8
——————————————————————
2010-15-05 18:00:00 | 18.5
——————————————————————
2010-15-06 00:00:00 | 14.3
——————————————————————
2010-15-06 06:00:00 | 15.5
——————————————————————
。。。
请各位指点一下 展开
—————————————————————————————————————————
日期 | 数据
—————————————————————————————————————————
2010-12-05 | 12.5*14.0*15.8*18.5
—————————————————————————————————————————
2010-12-06 | 14.3*15.5*17.5*19.0
—————————————————————————————————————————
。。。
日期对应的以*为分隔符的数据是每6小时一个时间点的数据,每天4个点,现在希望得到如下的结果集
时间 | 数据
——————————————————————
2010-12-05 00:00:00 | 12.5
——————————————————————
2010-15-05 06:00:00 | 14.0
——————————————————————
2010-15-05 12:00:00 | 15.8
——————————————————————
2010-15-05 18:00:00 | 18.5
——————————————————————
2010-15-06 00:00:00 | 14.3
——————————————————————
2010-15-06 06:00:00 | 15.5
——————————————————————
。。。
请各位指点一下 展开
2个回答
展开全部
Connected to:
Oracle Database 10g Release 10.1.0.2.0 - Production
SQL> set serveroutput on
SQL> set linesize 32676
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL>--创建临时表tmp
SQL> CREATE global temporary TABLE tmp
2 ( rq date,
3 data varchar2(200)
4 )
5 on commit preserve rows ;
Table created.
SQL> --原数据:假设是每小时一个点,共24个点,日期不连续!
SQL> select * from test;
RQ DATA
------------------- --------------------------------------------------------------------------------------
2010-12-05 00:00:00 1*2*3*4*5*6*7*8*9*10*11*12*13*14*15*16*17*18*19*20*21*22*23*24
2010-12-08 00:00:00 11*22*33*44*55*66*77*88*99*100*110*120*130*140*150*160*170*180*190*200*210*220*230*240
SQL>--创建过程:
create or replace procedure p_test_tmp(resultSet out sys_refcursor)
as
str2 varchar2(4000);
num number;
begin
for rs in(select rq,data FROM test ) loop
str2:='*'||trim(rs.data)||'*';
num:=length(str2)-length(replace(rs.data,'*',''))-1;
for i in 1..num loop
insert into tmp(rq,data)
select rs.rq+(i-1)/24, --这里是24个点,是每小时一个点的,根据你间隔时间段来修改
substr(str2,instr(str2,'*',1,i)+1,abs(instr(str2,'*',1,i+1)-instr(str2,'*',1,i)-1))
from dual ;
if mod(i,10)=0 then --批量提交
commit;
end if ;
end loop;
end loop;
open resultset for select * from tmp;
exception when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
end;
Procedure created.
SQL> --测试:
SQL> var cur refcursor
SQL> exec p_test_tmp(:cur);
PL/SQL procedure successfully completed.
SQL> print cur
RQ DATA
------------------- ------------------------------
2010-12-05 00:00:00 1
2010-12-05 01:00:00 2
2010-12-05 02:00:00 3
2010-12-05 03:00:00 4
2010-12-05 04:00:00 5
2010-12-05 05:00:00 6
2010-12-05 06:00:00 7
2010-12-05 07:00:00 8
2010-12-05 08:00:00 9
2010-12-05 09:00:00 10
2010-12-05 10:00:00 11
RQ DATA
------------------- ------------------------------
2010-12-05 11:00:00 12
2010-12-05 12:00:00 13
2010-12-05 13:00:00 14
2010-12-05 14:00:00 15
2010-12-05 15:00:00 16
2010-12-05 16:00:00 17
2010-12-05 17:00:00 18
2010-12-05 18:00:00 19
2010-12-05 19:00:00 20
2010-12-05 20:00:00 21
2010-12-05 21:00:00 22
RQ DATA
------------------- ------------------------------
2010-12-05 22:00:00 23
2010-12-05 23:00:00 24
2010-12-08 00:00:00 11
2010-12-08 01:00:00 22
2010-12-08 02:00:00 33
2010-12-08 03:00:00 44
2010-12-08 04:00:00 55
2010-12-08 05:00:00 66
2010-12-08 06:00:00 77
2010-12-08 07:00:00 88
2010-12-08 08:00:00 99
RQ DATA
------------------- ------------------------------
2010-12-08 09:00:00 100
2010-12-08 10:00:00 110
2010-12-08 11:00:00 120
2010-12-08 12:00:00 130
2010-12-08 13:00:00 140
2010-12-08 14:00:00 150
2010-12-08 15:00:00 160
2010-12-08 16:00:00 170
2010-12-08 17:00:00 180
2010-12-08 18:00:00 190
2010-12-08 19:00:00 200
RQ DATA
------------------- ------------------------------
2010-12-08 20:00:00 210
2010-12-08 21:00:00 220
2010-12-08 22:00:00 230
2010-12-08 23:00:00 240
48 rows selected.
SQL>
Oracle Database 10g Release 10.1.0.2.0 - Production
SQL> set serveroutput on
SQL> set linesize 32676
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL>--创建临时表tmp
SQL> CREATE global temporary TABLE tmp
2 ( rq date,
3 data varchar2(200)
4 )
5 on commit preserve rows ;
Table created.
SQL> --原数据:假设是每小时一个点,共24个点,日期不连续!
SQL> select * from test;
RQ DATA
------------------- --------------------------------------------------------------------------------------
2010-12-05 00:00:00 1*2*3*4*5*6*7*8*9*10*11*12*13*14*15*16*17*18*19*20*21*22*23*24
2010-12-08 00:00:00 11*22*33*44*55*66*77*88*99*100*110*120*130*140*150*160*170*180*190*200*210*220*230*240
SQL>--创建过程:
create or replace procedure p_test_tmp(resultSet out sys_refcursor)
as
str2 varchar2(4000);
num number;
begin
for rs in(select rq,data FROM test ) loop
str2:='*'||trim(rs.data)||'*';
num:=length(str2)-length(replace(rs.data,'*',''))-1;
for i in 1..num loop
insert into tmp(rq,data)
select rs.rq+(i-1)/24, --这里是24个点,是每小时一个点的,根据你间隔时间段来修改
substr(str2,instr(str2,'*',1,i)+1,abs(instr(str2,'*',1,i+1)-instr(str2,'*',1,i)-1))
from dual ;
if mod(i,10)=0 then --批量提交
commit;
end if ;
end loop;
end loop;
open resultset for select * from tmp;
exception when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
end;
Procedure created.
SQL> --测试:
SQL> var cur refcursor
SQL> exec p_test_tmp(:cur);
PL/SQL procedure successfully completed.
SQL> print cur
RQ DATA
------------------- ------------------------------
2010-12-05 00:00:00 1
2010-12-05 01:00:00 2
2010-12-05 02:00:00 3
2010-12-05 03:00:00 4
2010-12-05 04:00:00 5
2010-12-05 05:00:00 6
2010-12-05 06:00:00 7
2010-12-05 07:00:00 8
2010-12-05 08:00:00 9
2010-12-05 09:00:00 10
2010-12-05 10:00:00 11
RQ DATA
------------------- ------------------------------
2010-12-05 11:00:00 12
2010-12-05 12:00:00 13
2010-12-05 13:00:00 14
2010-12-05 14:00:00 15
2010-12-05 15:00:00 16
2010-12-05 16:00:00 17
2010-12-05 17:00:00 18
2010-12-05 18:00:00 19
2010-12-05 19:00:00 20
2010-12-05 20:00:00 21
2010-12-05 21:00:00 22
RQ DATA
------------------- ------------------------------
2010-12-05 22:00:00 23
2010-12-05 23:00:00 24
2010-12-08 00:00:00 11
2010-12-08 01:00:00 22
2010-12-08 02:00:00 33
2010-12-08 03:00:00 44
2010-12-08 04:00:00 55
2010-12-08 05:00:00 66
2010-12-08 06:00:00 77
2010-12-08 07:00:00 88
2010-12-08 08:00:00 99
RQ DATA
------------------- ------------------------------
2010-12-08 09:00:00 100
2010-12-08 10:00:00 110
2010-12-08 11:00:00 120
2010-12-08 12:00:00 130
2010-12-08 13:00:00 140
2010-12-08 14:00:00 150
2010-12-08 15:00:00 160
2010-12-08 16:00:00 170
2010-12-08 17:00:00 180
2010-12-08 18:00:00 190
2010-12-08 19:00:00 200
RQ DATA
------------------- ------------------------------
2010-12-08 20:00:00 210
2010-12-08 21:00:00 220
2010-12-08 22:00:00 230
2010-12-08 23:00:00 240
48 rows selected.
SQL>
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询