如何得到想要的数据(有点难)？

Question

有表testetimekong,其中有数据：
dhhm      youhlx     s_time                  e_time
86902899 101       2002-4-29 13:32:13
86902899 102       2002-4-29 13:32:13
85175613 102       2002-4-29 14:19:47
86920559 101       2002-4-29 14:26:07
86920559 102       2002-4-29 14:26:07
85065611 101       2002-4-29 14:48:29
85065611 102       2002-4-29 14:48:29
86905225 101       2002-4-29 14:58:42
86905225 102       2002-4-29 14:58:42
88837223 102       2002-4-29 14:51:48
该表中的e_time时间均为空，要求将e_time表的数据填满，规则如下：
有另外一张静态表，ayouh
有字段scode,effect_time,time_unit字段，其中scode就是youhlx代码，
effect_time表示有效期限，time_unit表示单位时段，
time_unit有值D，M，Y分别表示天，月，年
如查询出这样的数据：
dhhm      youhlx  s_time               e_time  effect_time  time_unit
86902899 101    2002-4-29 13:32:13            0            Y
86902899 102    2002-4-29 13:32:13            2            Y
85175613 102    2002-4-29 14:19:47            1            D
86920559 101    2002-4-29 14:26:07            0            D
86920559 102    2002-4-29 14:26:07            3            M
85065611 101    2002-4-29 14:48:29            0            M
85065611 102    2002-4-29 14:48:29            0            M
86905225 101    2002-4-29 14:58:42            0            M
86905225 102    2002-4-29 14:58:42            0            M
88837223 102    2002-4-29 14:51:48            0            M
就应该得出这样的数据：
dhhm      youhlx  s_time               e_time              effect_time  time_unit
86902899 101    2002-4-29 13:32:13    2002-4-29 13:32:13  0            Y
86902899 102    2002-4-29 13:32:13    2003-4-29 13:32:13  1            Y
85175613 102    2002-4-29 14:19:47    2002-4-29 14:19:47  0            D
86920559 101    2002-4-29 14:26:07    2002-5-01 13:32:13  2            D
86920559 102    2002-4-29 14:26:07    2002-7-29 14:26:07  3            M
85065611 101    2002-4-29 14:48:29    2002-4-29 14:48:29  0            M
85065611 102    2002-4-29 14:48:29    2002-4-29 14:48:29  0            M
86905225 101    2002-4-29 14:58:42    2002-4-29 14:58:42  0            M
86905225 102    2002-4-29 14:58:42    2002-4-29 14:58:42  0            M
88837223 102    2002-4-29 14:51:48    2002-4-29 14:51:48  0            M
请该问题如何解决？用怎么的存储过程才能解决？

Answer 1

估计比较慢
SELECT a.dhhm,a.youhlx,a.s_time,
Decode(b.time_unit,'Y',To_Date(To_Char(to_number(to_char(the_Date,'yyyy')+b.effect_time))||To_Char(the_Date,'mmddhh24miss'),'yyyymmddhh24miss'),
'M',To_Date(To_Char(to_number(to_char(the_Date,'yyyymm')+b.effect_time))||To_Char(the_Date,'ddhh24miss'),'yyyymmddhh24miss'),
'Y',To_Date(To_Char(to_number(to_char(the_Date,'yyyymmdd')+b.effect_time))||To_Char(the_Date,'hh24miss'),'yyyymmddhh24miss')) AS e_time,
b.effect_time,b.time_unit  FROM testetimekong a,ayouh b

Answer 2

SELECT a.dhhm,a.youhlx,a.s_time,
Decode(b.time_unit,'Y',To_Date(To_Char(to_number(to_char(s_time,'yyyy')+b.effect_time))||To_Char(s_time,'mmddhh24miss'),'yyyymmddhh24miss'),
'M',To_Date(To_Char(to_number(to_char(s_time,'yyyymm')+b.effect_time))||To_Char(s_time,'ddhh24miss'),'yyyymmddhh24miss'),
'Y',To_Date(To_Char(to_number(to_char(s_time,'yyyymmdd')+b.effect_time))||To_Char(s_time,'hh24miss'),'yyyymmddhh24miss')) AS e_time,
b.effect_time,b.time_unit  FROM testetimekong a,ayouh b

Answer 3

SELECT a.dhhm,a.youhlx,a.s_time,
s_time+Decode(b.time_unit,'Y',add_months(s_time,b.effect_time*12),
'M',add_months(s_time,b.effect_time),
'D',s_time+b.effect_time,s_time) AS e_time,
b.effect_time,b.time_unit  FROM testetimekong a,ayouh b where a.youhlx=b.scode;

Answer 4

SELECT a.dhhm,
       a.youhlx,
       a.s_time,
       Decode(b.time_unit,
              'Y',
              To_Date(To_Char(to_number(to_char(a.s_time, 'yyyy') +
                                        b.effect_time)) ||
                      To_Char(a.s_time, 'mmddhh24miss'),
                      'yyyymmddhh24miss'),
              'M',
              To_Date(To_Char(to_number(to_char(a.s_time, 'yyyymm') +
                                        b.effect_time)) ||
                      To_Char(a.s_time, 'ddhh24miss'),
                      'yyyymmddhh24miss'),
              'Y',
              To_Date(To_Char(to_number(to_char(a.s_time, 'yyyymmdd') +
                                        b.effect_time)) ||
                      To_Char(a.s_time, 'hh24miss'),
                      'yyyymmddhh24miss')) AS e_time,
       b.effect_time,
       b.time_unit
  FROM testetimekong a, ayouh b
呵呵，谢谢你yuyu1980！接分