--试试 select * from tb t where exists(select null from tb t2 where t2.datetime>=t.datetime+1/24 or t2.datetime<=t.datetime-1/24)
select t1.* from table_t t1,table_t t2 where t1.datetime+numtodsinterval(1,'hour')=t2.datetime; /*where t1.datetime+1/24=t2.datetime;*/
with test1 as( select 1 id,'aaa' tname,'2008-12-01 12:12:09' datetime from dual union all select 2 id,'abc' tname,'2008-12-01 13:12:09' datetime from dual union all select 3 id,'bcd' tname,'2008-12-01 01:12:09' datetime from dual union all select 4 id,'der' tname,'2008-12-01 22:12:09' datetime from dual union all select 5 id,'erf' tname,'2008-12-01 22:12:09' datetime from dual) select * from test1 t1 where exists( select 1 from test1 t2 where to_timestamp(t1.datetime,'yyyy-MM-dd hh24:mi:ss') = to_timestamp(t2.datetime,'yyyy-MM-dd hh24:mi:ss')+1/24);
with tb as( select 1 id,'aaa' name,to_date('2008-12-01 12:12:09','yyyy-mm-dd hh24:mi:ss') dt from dual union all select 2,'bbb',to_date('2008-12-01 13:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all select 3,'ccc',to_date('2008-12-01 11:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all select 4,'ddd',to_date('2008-12-01 21:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all select 5,'fff',to_date('2008-12-01 16:12:09','yyyy-mm-dd hh24:mi:ss') from dual) select distinct(t2.id) id,t2.name name,to_char(t2.dt,'yyyy-mm-dd hh24:mi:ss') dt from tb t1,tb t2 where t1.dt+numtodsinterval(1,'hour')=t2.dt or t1.dt+numtodsinterval(-1,'hour')=t2.dt; -- ID NAME DT ---------- ---- ------------------- 1 aaa 2008-12-01 12:12:09 2 bbb 2008-12-01 13:12:09 3 ccc 2008-12-01 11:12:09
--现在并不是前面三条相差1小时 SQL> with tb as( 2 select 1 id,'aaa' name,to_date('2008-12-01 12:12:09','yyyy-mm-dd hh24:mi:ss') dt from dual union all 3 select 2,'bbb',to_date('2008-12-01 13:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all 4 select 3,'ccc',to_date('2008-12-01 11:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all 5 select 4,'ddd',to_date('2008-12-01 21:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all 6 select 5,'fff',to_date('2008-12-01 16:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all 7 select 6,'eee',to_date('2008-12-02 17:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all 8 select 7,'ggg',to_date('2008-12-01 22:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all 9 select 8,'hhh',to_date('2008-12-02 18:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all 10 select 9,'iii',to_date('2011-4-14 10:51:52','yyyy-mm-dd hh24:mi:ss') from dual) 11 select distinct(t2.id) id,t2.name name,to_char(t2.dt,'yyyy-mm-dd hh24:mi:ss') dt 12 from tb t1,tb t2 13 where t1.dt+numtodsinterval(1,'hour')=t2.dt or 14 t1.dt+numtodsinterval(-1,'hour')=t2.dt 15 order by dt; ID NAME DT ---------- ---- ------------------- 3 ccc 2008-12-01 11:12:09 1 aaa 2008-12-01 12:12:09 2 bbb 2008-12-01 13:12:09 4 ddd 2008-12-01 21:12:09 7 ggg 2008-12-01 22:12:09 6 eee 2008-12-02 17:12:09 8 hhh 2008-12-02 18:12:09
SQL> with tb as( 2 select 1 id,'aaa' name,to_date('2008-12-01 12:12:09','yyyy-mm-dd hh24:mi:ss') dt from dual union all 3 select 2,'bbb',to_date('2008-12-01 13:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all 4 select 3,'ccc',to_date('2008-12-01 11:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all 5 select 4,'ddd',to_date('2008-12-01 21:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all 6 select 5,'fff',to_date('2008-12-01 16:12:09','yyyy-mm-dd hh24:mi:ss') from dual) 7 select * 8 from tb a 9 where exists (select 1 10 from tb b 11 where a.dt = b.dt + 1 / 24 12 or a.dt = b.dt - 1 / 24);
ID NAME DT ---------- ---- ------------------- 1 aaa 2008-12-1 12:12:09 2 bbb 2008-12-1 13:12:09 3 ccc 2008-12-1 11:12:09
--试试
select *
from tb t
where exists(select null from tb t2
where t2.datetime>=t.datetime+1/24
or t2.datetime<=t.datetime-1/24)
select t1.*
from table_t t1,table_t t2
where t1.datetime+numtodsinterval(1,'hour')=t2.datetime;
/*where t1.datetime+1/24=t2.datetime;*/
select 1 id,'aaa' tname,'2008-12-01 12:12:09' datetime from dual union all
select 2 id,'abc' tname,'2008-12-01 13:12:09' datetime from dual union all
select 3 id,'bcd' tname,'2008-12-01 01:12:09' datetime from dual union all
select 4 id,'der' tname,'2008-12-01 22:12:09' datetime from dual union all
select 5 id,'erf' tname,'2008-12-01 22:12:09' datetime from dual)
select * from test1 t1 where exists(
select 1
from test1 t2
where
to_timestamp(t1.datetime,'yyyy-MM-dd hh24:mi:ss') = to_timestamp(t2.datetime,'yyyy-MM-dd hh24:mi:ss')+1/24);
我希望的是查出所有时间相差一个小时的记录啊!
比如下面5条记录:
1 ,'aaa','2008-12-01 12:12:09'
2 ,'bbb','2008-12-01 13:12:09'
3 ,'ccc','2008-12-01 11:12:09'
4 ,'ddd','2008-12-01 21:12:09'
5 ,'fff','2008-12-01 16:12:09'只把前三条查询出来!
with tb as(
select 1 id,'aaa' name,to_date('2008-12-01 12:12:09','yyyy-mm-dd hh24:mi:ss') dt from dual union all
select 2,'bbb',to_date('2008-12-01 13:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all
select 3,'ccc',to_date('2008-12-01 11:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all
select 4,'ddd',to_date('2008-12-01 21:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all
select 5,'fff',to_date('2008-12-01 16:12:09','yyyy-mm-dd hh24:mi:ss') from dual)
select distinct(t2.id) id,t2.name name,to_char(t2.dt,'yyyy-mm-dd hh24:mi:ss') dt
from tb t1,tb t2
where t1.dt+numtodsinterval(1,'hour')=t2.dt or
t1.dt+numtodsinterval(-1,'hour')=t2.dt;
--
ID NAME DT
---------- ---- -------------------
1 aaa 2008-12-01 12:12:09
2 bbb 2008-12-01 13:12:09
3 ccc 2008-12-01 11:12:09
--现在并不是前面三条相差1小时
SQL> with tb as(
2 select 1 id,'aaa' name,to_date('2008-12-01 12:12:09','yyyy-mm-dd hh24:mi:ss') dt from dual union all
3 select 2,'bbb',to_date('2008-12-01 13:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all
4 select 3,'ccc',to_date('2008-12-01 11:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all
5 select 4,'ddd',to_date('2008-12-01 21:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all
6 select 5,'fff',to_date('2008-12-01 16:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all
7 select 6,'eee',to_date('2008-12-02 17:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all
8 select 7,'ggg',to_date('2008-12-01 22:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all
9 select 8,'hhh',to_date('2008-12-02 18:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all
10 select 9,'iii',to_date('2011-4-14 10:51:52','yyyy-mm-dd hh24:mi:ss') from dual)
11 select distinct(t2.id) id,t2.name name,to_char(t2.dt,'yyyy-mm-dd hh24:mi:ss') dt
12 from tb t1,tb t2
13 where t1.dt+numtodsinterval(1,'hour')=t2.dt or
14 t1.dt+numtodsinterval(-1,'hour')=t2.dt
15 order by dt; ID NAME DT
---------- ---- -------------------
3 ccc 2008-12-01 11:12:09
1 aaa 2008-12-01 12:12:09
2 bbb 2008-12-01 13:12:09
4 ddd 2008-12-01 21:12:09
7 ggg 2008-12-01 22:12:09
6 eee 2008-12-02 17:12:09
8 hhh 2008-12-02 18:12:09
2 select 1 id,'aaa' name,to_date('2008-12-01 12:12:09','yyyy-mm-dd hh24:mi:ss') dt from dual union all
3 select 2,'bbb',to_date('2008-12-01 13:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all
4 select 3,'ccc',to_date('2008-12-01 11:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all
5 select 4,'ddd',to_date('2008-12-01 21:12:09','yyyy-mm-dd hh24:mi:ss') from dual union all
6 select 5,'fff',to_date('2008-12-01 16:12:09','yyyy-mm-dd hh24:mi:ss') from dual)
7 select *
8 from tb a
9 where exists (select 1
10 from tb b
11 where a.dt = b.dt + 1 / 24
12 or a.dt = b.dt - 1 / 24);
ID NAME DT
---------- ---- -------------------
1 aaa 2008-12-1 12:12:09
2 bbb 2008-12-1 13:12:09
3 ccc 2008-12-1 11:12:09