17,086
社区成员
发帖
与我相关
我的任务
分享请教下一个单表的sql语法，某个时间字段两条记录在一定范围内的所以记录
表结构：主键、单号、时间（字符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
主键 单号 时间
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
...全文
请发表友善的回复…
发表回复
不写代码的钦 2016-09-02
- 打赏
- 举报
谢谢，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
js14982 2016-09-02
- 打赏
- 举报
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;
卖水果的net 2016-09-02
- 打赏
- 举报
2# 的语句，修改一下 over 子句就可以了；
aiq 2016-09-02
- 打赏
- 举报
谢谢，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小时内的记录，谢谢
js14982 2016-09-02
- 打赏
- 举报
那我来个自连接的
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;
mayanzs 2016-09-02
- 打赏
- 举报
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;
ghx287524027 2016-09-02
- 打赏
- 举报
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不写代码的钦 2016-09-02
- 打赏
- 举报
首先,你的主键为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
