请教一个关于同一张表相同字段的数据比较问题！

[chenshicss]

有个需求，需要比较同一张表的一个时间字段里的数据，查询出时间相差1个小时的记录！


请问大虾们有什么好的办法吗？

[真的不知道取什么名]

--试试
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)

[blue2u]

自连接

[304的的哥]

select t1.*
from table_t t1,table_t t2
where t1.datetime+numtodsinterval(1,'hour')=t2.datetime;
/*where t1.datetime+1/24=t2.datetime;*/

[winer2008]

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);

[chenshicss]

谢谢大家！

我1天后来结贴

[chenshicss]

还是不行啊！
我希望的是查出所有时间相差一个小时的记录啊！
比如下面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'

只把前三条查询出来！

[304的的哥]

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

[yejihui9527]

[Quote=引用 6 楼 chenshicss 的回复:]
还是不行啊！
我希望的是查出所有时间相差一个小时的记录啊！
比如下面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……
[/Quote]
因为只有前面3条 相差一小时

[304的的哥]

--现在并不是前面三条相差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

[minitoy]

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

[chenshicss]

谢谢大家的帮助！
解决了！