请教下一个单表的sql语法，某个时间字段两条记录在一定范围内的所以记录

[aiq]

表结构：主键、单号、时间（字符12位，如201609011231）；

主键 单号 时间
1 001 201609010831
2 001 201609010910
3 001 201609011031
4 001 201609011135
5 001 201609011215
6 001 201609011331
7 001 201609011432
8 002 201609011531
9 002 201609011210

我现在想查 单号相同的两条（不知道如果三条以上可不可以查）记录时间在1个小时范围内 的所有记录（放到另外一张相同的表中）比如，上面符合要求的是
1 001 201609010831
2 001 201609010910
4 001 201609011135
5 001 201609011215
8 002 201609011531
9 002 201609011210

[不写代码的钦]

首先,你的主键为8,9的记录不在满足"相同单号,时间在1小时内",所以满足条件的是主键为1,2,4,5的记录; 这里假定你的时间为varchar类型的.sql如下,自己修改下,应该可以得到你的答案 select 主键,单号,时间 from ( select p.主键,p.单号,p.时间,lead(p.时间) over(partition by p.单号 order by 时间) forward ,lead(p.时间) over(partition by p.单号 order by 时间 desc) back from 表名 p ) where to_date(forward,'yyyymmddhh24mi')-to_date(时间,'yyyymmddhh24mi')<=1/24 or to_date(back,'yyyymmddhh24mi')-to_date(时间,'yyyymmddhh24mi')>=-1/24

[ghx287524027]

with t as (
select 1 as id,'001' as dno,'201609010831'as sj from dual
union all
select 2 as id,'001' as dno,'201609010910'as sj from dual
union all
select 3 as id,'001' as dno,'201609011031'as sj from dual
union all
select 4 as id,'001' as dno,'201609011135'as sj from dual
union all
select 5 as id,'001' as dno,'201609011215'as sj from dual
union all
select 6 as id,'001' as dno,'201609011331'as sj from dual
union all
select 7 as id,'001' as dno,'201609011432'as sj from dual
union all
select 8 as id,'002' as dno,'201609011531'as sj from dual
union all
select 9 as id,'002' as dno,'201609011210'as sj from dual
)

select t1.* from 
(
select id,dno,lag(TO_date(sj,'YYYY-MM-DD hh24:mi:ss'),1) over(partition by dno order by sj) p_sj,
TO_date(sj,'YYYY-MM-DD hh24:mi:ss') sj,
lead(TO_date(sj,'YYYY-MM-DD hh24:mi:ss'),1) over(partition by dno order by sj) n_sj
from t
) t1
where ROUND(TO_NUMBER(sj-p_sj)*24*60)<60 or ROUND(TO_NUMBER(n_sj-sj)*24*60)<60

对于id是8和9的两条记录，时间间隔已经大于1小时了怎么还在结果里？

[mayanzs]

with t as ( select 1 as id,'001' as dno,to_date('201609010831','yyyymmddhh24miss') as sj from dual union all select 2 as id,'001' as dno,to_date('201609010910','yyyymmddhh24miss') as sj from dual union all select 3 as id,'001' as dno,to_date('201609011031','yyyymmddhh24miss') as sj from dual union all select 4 as id,'001' as dno,to_date('201609011135','yyyymmddhh24miss') as sj from dual union all select 5 as id,'001' as dno,to_date('201609011215','yyyymmddhh24miss') as sj from dual union all select 6 as id,'001' as dno,to_date('201609011331','yyyymmddhh24miss') as sj from dual union all select 7 as id,'001' as dno,to_date('201609011432','yyyymmddhh24miss') as sj from dual union all select 8 as id,'002' as dno,to_date('201609011531','yyyymmddhh24miss') as sj from dual union all select 9 as id,'002' as dno,to_date('201609011210','yyyymmddhh24miss') as sj from dual), t1 as (select t.*,count(1) over (patition by dno order by sj range interval '1' hour preceding)+count(1) over (patition by dno order by sj desc range interval '1' hour preceding) cc from t) select id,dno,sj from t1 where cc>2;

[js14982]

那我来个自连接的

with t as (
select 1 as id,'001' as dno,to_date('201609010831','yyyymmddhh24miss') as sj from dual union all
select 2 as id,'001' as dno,to_date('201609010910','yyyymmddhh24miss') as sj from dual union all
select 3 as id,'001' as dno,to_date('201609011031','yyyymmddhh24miss') as sj from dual union all
select 4 as id,'001' as dno,to_date('201609011135','yyyymmddhh24miss') as sj from dual union all
select 5 as id,'001' as dno,to_date('201609011215','yyyymmddhh24miss') as sj from dual union all
select 6 as id,'001' as dno,to_date('201609011331','yyyymmddhh24miss') as sj from dual union all
select 7 as id,'001' as dno,to_date('201609011432','yyyymmddhh24miss') as sj from dual union all
select 8 as id,'002' as dno,to_date('201609011531','yyyymmddhh24miss') as sj from dual union all
select 9 as id,'002' as dno,to_date('201609011210','yyyymmddhh24miss') as sj from dual)
select t1.*
from t t1,t t2
where t1.dno=t2.dno
  and t1.id<>t2.id
  and abs(t1.sj-t2.sj)<1/24
order by 1;

[aiq]

引用 1 楼 arlen1990 的回复:

首先,你的主键为8,9的记录不在满足"相同单号,时间在1小时内",所以满足条件的是主键为1,2,4,5的记录; 这里假定你的时间为varchar类型的.sql如下,自己修改下,应该可以得到你的答案 select 主键,单号,时间 from ( select p.主键,p.单号,p.时间,lead(p.时间) over(partition by p.单号 order by 时间) forward ,lead(p.时间) over(partition by p.单号 order by 时间 desc) back from 表名 p ) where to_date(forward,'yyyymmddhh24mi')-to_date(时间,'yyyymmddhh24mi')<=1/24 or to_date(back,'yyyymmddhh24mi')-to_date(时间,'yyyymmddhh24mi')>=-1/24

谢谢，8,9记录是我搞错了，刚才执行了，结果应该是对的，但发现我刚才讲的条件有问题，现在要加条件，部门 主键 部门 单号 时间 1 1 001 201609010831 2 1 001 201609010910 3 1 001 201609011031 4 1 001 201609011135 5 1 001 201609011215 6 2 001 201609011331 7 2 001 201609011432 8 1 002 201609011531 9 1 002 201609011210 同一部门内，相同单号 时间在1小时内的记录，谢谢

[卖水果的net]

引用 5 楼 aiq 的回复:

同一部门内，相同单号 时间在1小时内的记录，谢谢

2# 的语句，修改一下 over 子句就可以了；

[js14982]

with t as (
select 1 as id,1 bm,'001' as dno,to_date('201609010831','yyyymmddhh24miss') as sj from dual union all
select 2 as id,1 bm,'001' as dno,to_date('201609010910','yyyymmddhh24miss') as sj from dual union all
select 3 as id,1 bm,'001' as dno,to_date('201609011031','yyyymmddhh24miss') as sj from dual union all
select 4 as id,1 bm,'001' as dno,to_date('201609011135','yyyymmddhh24miss') as sj from dual union all
select 5 as id,1 bm,'001' as dno,to_date('201609011215','yyyymmddhh24miss') as sj from dual union all
select 6 as id,2 bm,'001' as dno,to_date('201609011331','yyyymmddhh24miss') as sj from dual union all
select 7 as id,2 bm,'001' as dno,to_date('201609011432','yyyymmddhh24miss') as sj from dual union all
select 8 as id,1 bm,'002' as dno,to_date('201609011531','yyyymmddhh24miss') as sj from dual union all
select 9 as id,1 bm,'002' as dno,to_date('201609011210','yyyymmddhh24miss') as sj from dual)
select t1.*
from t t1,t t2
where t1.dno=t2.dno
  and t1.bm=t2.bm     --加一个部门匹配就好
  and t1.id<>t2.id
  and abs(t1.sj-t2.sj)<1/24
order by 1;

[不写代码的钦]

引用 5 楼 aiq 的回复:

[quote=引用 1 楼 arlen1990 的回复:] 首先,你的主键为8,9的记录不在满足"相同单号,时间在1小时内",所以满足条件的是主键为1,2,4,5的记录; 这里假定你的时间为varchar类型的.sql如下,自己修改下,应该可以得到你的答案 select 主键,单号,时间 from ( select p.主键,p.单号,p.时间,lead(p.时间) over(partition by p.单号 order by 时间) forward ,lead(p.时间) over(partition by p.单号 order by 时间 desc) back from 表名 p ) where to_date(forward,'yyyymmddhh24mi')-to_date(时间,'yyyymmddhh24mi')<=1/24 or to_date(back,'yyyymmddhh24mi')-to_date(时间,'yyyymmddhh24mi')>=-1/24

谢谢，8,9记录是我搞错了，刚才执行了，结果应该是对的，但发现我刚才讲的条件有问题，现在要加条件，部门 主键 部门 单号 时间 1 1 001 201609010831 2 1 001 201609010910 3 1 001 201609011031 4 1 001 201609011135 5 1 001 201609011215 6 2 001 201609011331 7 2 001 201609011432 8 1 002 201609011531 9 1 002 201609011210 同一部门内，相同单号 时间在1小时内的记录，谢谢[/quote] 那就加上部门咯 select 主键,部门,单号,时间 from ( select p.主键,p.部门,p.单号,p.时间,lead(p.时间) over(partition by p.单号,p.部门 order by 时间) forward ,lead(p.时间) over(partition by p.单号,p.部门 order by 时间 desc) back from 表名 p ) where to_date(forward,'yyyymmddhh24mi')-to_date(时间,'yyyymmddhh24mi')<=1/24 or to_date(back,'yyyymmddhh24mi')-to_date(时间,'yyyymmddhh24mi')>=-1/24