比如一个表 有两个字段
ID datetime
1 2010-01-2 19:30:00
2 2010-01-2 18:30:00
3 2010-01-2 18:10:00
4 2010-01-2 20:10:00
5 2010-01-2 19:20:00数据如上,我现在想用SQL语句,对所有记录按照时间排序,计算出两个最近时间间隔大于半小时的次数比如上面数据
排序得出结果为
3 2010-01-2 18:10:00
2 2010-01-2 18:30:00
5 2010-01-2 19:20:00
1 2010-01-2 19:30:00
4 2010-01-2 20:10:00
时间升序两条相邻记录之间相隔超过 半小时的 共有2条
即 (2 2010-01-2 18:30:00) 与 (5 2010-01-2 19:20:00)和
(1 2010-01-2 19:30:00)与(4 2010-01-2 20:10:00) 相隔半小时上
ID datetime
1 2010-01-2 19:30:00
2 2010-01-2 18:30:00
3 2010-01-2 18:10:00
4 2010-01-2 20:10:00
5 2010-01-2 19:20:00数据如上,我现在想用SQL语句,对所有记录按照时间排序,计算出两个最近时间间隔大于半小时的次数比如上面数据
排序得出结果为
3 2010-01-2 18:10:00
2 2010-01-2 18:30:00
5 2010-01-2 19:20:00
1 2010-01-2 19:30:00
4 2010-01-2 20:10:00
时间升序两条相邻记录之间相隔超过 半小时的 共有2条
即 (2 2010-01-2 18:30:00) 与 (5 2010-01-2 19:20:00)和
(1 2010-01-2 19:30:00)与(4 2010-01-2 20:10:00) 相隔半小时上
解决方案 »
- 数据库8i如何导入到10g上
- ORACLE查询试图提示 ORA-01722: 无效数字
- oracle 数据导入问题
- 用sys编译出错!DBMS_EXPORT_EXTENSION compile
- 求救Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169869568)错误
- 临时表的数据重复增加问题
- error C2043: illegal break
- 如何去oracle 获取列名和该列的值
- 我将form发布到IAS,通过WEB访问时,总是提示我输入用户名密码
- pro c相关的一些简单问题,再问:)
- select * from dept 当前 deptno 的 dname 值取 deptno+1 的 dname,如果是最大的,取最小的那个
- sql查询请教
with tmp as
(
select 1 id, to_date('2010-01-02 19:30:00','yyyy-mm-dd hh24:mi:ss') datetime from dual union all
select 2 id, to_date('2010-01-02 18:30:00','yyyy-mm-dd hh24:mi:ss') datetime from dual union all
select 3 id, to_date('2010-01-02 18:10:00','yyyy-mm-dd hh24:mi:ss') datetime from dual union all
select 4 id, to_date('2010-01-02 20:10:00','yyyy-mm-dd hh24:mi:ss') datetime from dual union all
select 5 id, to_date('2010-01-02 19:20:00','yyyy-mm-dd hh24:mi:ss') datetime from dual
)
select datetime2, datetime from (
select id, datetime, lag(datetime,1) over (order by datetime) datetime2
from tmp
)
where datetime - datetime2 > 1/48
order by id;DATETIME2 DATETIME
---------------------------- ----------------------------
2010-01-02 19:30:00 2010-01-02 20:10:00
2010-01-02 18:30:00 2010-01-02 19:20:00
select 1 id,to_date('2010-01-2 19:30:00','yyyy-mm-dd hh24:mi:ss') t from dual
union all
select 2 id,to_date('2010-01-2 18:30:00','yyyy-mm-dd hh24:mi:ss') t from dual
union all
select 3 id,to_date('2010-01-2 18:10:00','yyyy-mm-dd hh24:mi:ss') t from dual
union all
select 4 id,to_date('2010-01-2 20:10:00','yyyy-mm-dd hh24:mi:ss') t from dual
union all
select 5 id,to_date('2010-01-2 19:20:00','yyyy-mm-dd hh24:mi:ss') t from dual
)
select id,t,t1,t - t1,30/1440 from(
select id,t,lag(t) over(order by t) t1 from temp
) where t - t1 >= 30/1440
1 select a.datetime,b.dt from t_temp a,
2 (select id,lag(datetime,1) over(order by datetime) dt from t_temp) b
3* where a.id=b.id and (a.datetime-b.dt)*24*60*60>=1800
SQL> /DATETIME DT
------------------- -------------------
2010-01-02 19:20:00 2010-01-02 18:30:00
2010-01-02 20:10:00 2010-01-02 19:30:00