Oracle SQL-如何按时间限制相关记录

罗斯卡特

给定下面列出的数据,如何仅选择记录,为此:

a)至少有1张相同的client_id先前的票证存在,并且

b)每张机票的最大时差不得超过14天。换句话说,如果票证具有a)中所述的后继者,并且该后继者是在> 14天后创建的,则不得考虑该后继者。

create table tickets (
ticket_id number,
client_id number,
start_time date);

insert into tickets values (1,1,to_date('201601011330','yyyymmddhh24mi'));
insert into tickets values (2,1,to_date('201601021320','yyyymmddhh24mi'));
insert into tickets values (3,1,to_date('201601101330','yyyymmddhh24mi'));
insert into tickets values (4,1,to_date('201603101330','yyyymmddhh24mi'));
insert into tickets values (5,2,to_date('201601011630','yyyymmddhh24mi'));
insert into tickets values (6,2,to_date('201601201330','yyyymmddhh24mi'));
insert into tickets values (7,3,to_date('201602011330','yyyymmddhh24mi'));
insert into tickets values (8,4,to_date('201602290000','yyyymmddhh24mi'));
insert into tickets values (9,4,to_date('201603011630','yyyymmddhh24mi'));
insert into tickets values (10,4,to_date('201604011120','yyyymmddhh24mi'));
insert into tickets values(11,4,to_date('201604101030','yyyymmddhh24mi'));
commit;
埃里希·基茨穆勒(Erich Kitzmueller)

没有分析功能也可以。

select * from tickets t1
  where exists (
    select 1 from tickets t2
      where t1.client_id = t2.client_id
        and t1.start_time>t2.start_time
        and t1.start_time<=t2.start_time+14
   );

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章