求一个SQL语句

Question

表结构如下：
—————————————————————————————————————————
日期                    |                            数据
—————————————————————————————————————————
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
——————————————————————
请各位指点一下

Answer 1

with tab as(
select '2010-12-05' rq,'12.5*14.0*15.8*18.5' data  from dual
union all 
select '2010-12-06','14.3*15.5*17.5*19.0' from dual
)
select distinct rq,substr('*'||data||'*',instr('*'||data||'*','*',1,level)+1,
          instr('*'||data||'*','*',1,level+1)-instr('*'||data||'*','*',1,level)-1) data
from tab
connect by
level <= length('*'||data||'*') - length(replace('*'||data||'*', '*', ''))-1
order by 1RQ          DATA
--------------------
2010-12-05  12.5
2010-12-05  14.0
2010-12-05  15.8
2010-12-05  18.5
2010-12-06  14.3
2010-12-06  15.5
2010-12-06  17.5
2010-12-06  19.0

Answer 2

--修改下：with tab as(
select To_Date('2010-12-05','yyyy-mm-dd') rq,'12.5*14.0*15.8*18.5' data  from dual
union all 
select To_Date('2010-12-06','yyyy-mm-dd'),'14.3*15.5*17.5*19.0' from dual
)
select distinct rq+(LEVEL-1)/4 日期,
       substr('*'||data||'*',instr('*'||data||'*','*',1,level)+1,
       instr('*'||data||'*','*',1,level+1)-instr('*'||data||'*','*',1,level)-1) 数据
from tab
connect by
level <= length('*'||data||'*') - length(replace('*'||data||'*', '*', ''))-1
order by 1日期                   数据
-------------------------------
2010.12.05 00:00:00    12.5
2010.12.05 06:00:00    14.0
2010.12.05 12:00:00    15.8
2010.12.05 18:00:00    18.5
2010.12.06 00:00:00    14.3
2010.12.06 06:00:00    15.5
2010.12.06 12:00:00    17.5
2010.12.06 18:00:00    19.0

Answer 3

顺便又学了一招，偷师一下，呵呵with tab as(
select '2010-12-05' rq,'12.5*14.0*15.8*18.5' data  from dual
union all 
select '2010-12-06','14.3*15.5*17.5*19.0' from dual
)
select distinct case when level =1 then rq||' 00:00:00'
                     when level =2 then rq||' 06:00:00'
                     when level =3 then rq||' 12:00:00'
                     when level =4 then rq||' 18:00:00'
                     else rq end rq
                ,regexp_substr(data||'*','.*?'||'[*]',1,level) data
from tab
     connect by
             level <= 
                   (length(data||'*') - length(replace(data||'*', '*')) ) / length('*')
                     -- 要截取的字符串 域数 （ 即， 以 '*' 分隔出来的字符串数量，有多少个）
                      -- 说明 (字符串长度 - 去除分隔符的长度) / 分隔符长度 = 分隔出的 字符串数量
                      -- 计算字符串长度时 ， 需再补上一个分隔符,因为 1|2  => ( 分隔符数量 + 1 = 分隔域 )
order by 1;

Answer 4

感谢上面兄弟的解答，不过还是有点问题：
1、如果日期不连续，用1楼的方法查询出来结果有问题；
2、最主要的是，当时间间隔缩小到1小时一个数据点时，sql执行不下去；
还请帮忙！谢谢

Answer 5

什么叫日期不连续？？？ 可以说上面我的SQL能满足你的要求，日期不连续也行，时间间隔是15分钟96个点也行只是效率低而已，因为用到递归connect by 当然会很慢
你要想实现同样的功能，效率要好点，最好写PLSQL去做，在过程中用循环游标读取来做！

Answer 6

Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> 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*240SQL>--创建过程：
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 curRQ                  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 11RQ                  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 22RQ                  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 99RQ                  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 200RQ                  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 24048 rows selected.SQL>