字段 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就是我要取得值
数据 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就是我要取得值
解决方案 »
- 请教个oracle的小问题
- ora后门技术讨论
- 关于绑定变量与非绑定变量的问题?
- 添加数据之后触发器把添加刚添加的数据某一列的数据改成0
- A query with LOB's requires OCI8 mode,but OCI7 mode is used
- 为什么用exp备份是用户名必须是 username as sysdba?imp导入时提示很多错误
- 不同数据库结构间如何导数,请给个sql例子,谢谢用的是oracel
- 求助高手,一个关于SEQUENCE的问题.在线等,谢谢了.
- 救救我吧!!!!
- 请教ORACLE安装后再改IP导致ORACLE出现问题怎么解决?
- 关于Oracle同步数据库问题?
- 关闭数据库报错!
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
-------------------- ------------------- -------------------
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:00scott@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
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;