初学者求教

[G66606495]

字段 NAME D_BEGIN_TIME D_END_TIME
数据 N1 2008-01-02 2008-01-04
N2 2008-01-14 2008-03-05
N3 2008-03-15 2008-03-22
N4 2008-03-25 2008-04-01
. . .
. . .
. . .

如何找到第N条记录的NAME值， 第N条记录的要求是 D_BEGIN_TIME最大且D_BEGIN_TIME减去第N-1条的D_END_TIME要等于10
比如上面数据N3条 D_BEGIN_TIME 2008-03-15 和N2的 D_END_TIME 2008-03-05 间隔天数为10 ，
N2条 D_BEGIN_TIME 2008-01-14 和N1的 D_END_TIME 2008-01-04的间隔天数也为10 ，但2008-03-15比2008-01-14 大
所以N3就是我要取得值

[hebo2005]

这个也不难,
select *
select *
from
(
select a.*,D_BEGIN_TIME-lag(D_END_TIME,1,D_BEGIN_TIME) (order by D_BEGIN_TIME) int_day
from a

)
where int_day=10
order by D_BEGIN_TIME desc)
where rownum=1

[codearts]

scott@ORA1> select * from t;

NAME                 D_BEGIN_TIME        D_END_TIME
-------------------- ------------------- -------------------
n1                   2008-01-02 00:00:00 2008-01-04 00:00:00
n2                   2008-01-14 00:00:00 2008-03-05 00:00:00
n3                   2008-03-15 00:00:00 2008-03-22 00:00:00
n4                   2008-03-25 00:00:00 2008-04-01 00:00:00

scott@ORA1> 
scott@ORA1> with x as (
  2  		select name
  3  				,d_begin_time
  4  				,d_end_time
  5  				,d_begin_time - lag(d_end_time) over(order by rownum) gaps
  6  		  from t
  7  )
  8  select *
  9    from (
 10  		select name
 11  			  ,d_begin_time
 12  			  ,d_end_time
 13  			  ,row_number() over(order by d_begin_time desc) rn
 14  		  from x
 15  		 where gaps = 10
 16  	   )
 17   where rn = 1;

NAME                 D_BEGIN_TIME        D_END_TIME                  RN
-------------------- ------------------- ------------------- ----------
n3                   2008-03-15 00:00:00 2008-03-22 00:00:00          1

[BlueskyWide]

select max(tab2.name)
  from (select rownum a, D_BEGIN_TIME c, D_END_TIME d from table_test) tab1,
       (select rownum a, D_BEGIN_TIME c, D_END_TIME d from table_test) tab2
 where tab2.D_BEGIN_TIME - tab1.D_END_TIME = 10
   and tab2.rownum - tab1.rownum = 1;

[Quote=引用楼主 G66606495 的帖子:]
字段 NAME D_BEGIN_TIME D_END_TIME
数据 N1 2008-01-02 2008-01-04
N2 2008-01-14 2008-03-05
N3 2008-03-15 2008-03-22
N4 2008-03-25 2008-04-01
. . .
. . .
. . .

如何找到第N条记录的NAME值， 第N条记录的要求是 D_BEGIN_TIME最大且D_BEGIN_TIME减去第N-1条的D_END_T…
[/Quote]

[mantisXF]

很多问题都是在原有问题上变了一下... ai~~[Quote=引用楼主 G66606495 的帖子:]
字段 NAME D_BEGIN_TIME D_END_TIME
数据 N1 2008-01-02 2008-01-04
N2 2008-01-14 2008-03-05
N3 2008-03-15 2008-03-22
N4 2008-03-25 2008-04-01
. . .
. . .
. . .

如何找到第N条记录的NAME值， 第N条记录的要求是 D_BEGIN_TIME最大且D_BEGIN_TIME减去第N-1条的D_END_T…
[/Quote]