oracle按要求查询，高分相送解决方案-Oracle教程-爱易网页

oracle按要求查询，高分相送解决方案

日期：2014-05-17　浏览次数：20820 次

oracle按要求查询，高分相送
表信息

连锁店编号会员卡号录入员工号录入终端录入时间
2012001001 12345678 2012000001 00000001 20120510 16:29:23
2012001001 12345678 2012000001 00000001 20120510 16:30:44
2012001001 12345678 2012000001 00000001 20120510 16:39:12
2012001004 12345678 2012000002 00000001 20120510 16:41:21
2012001004 32345678 2012000002 00000002 20120510 16:43:56
2012001004 22345678 2012000002 00000001 20120510 16:46:49
2012001004 52345678 2012000002 00000002 20120510 16:47:23
2012001001 32345678 2012000003 00000001 20120510 16:49:10
2012001002 82345678 2012000003 00000002 20120510 16:53:08
2012001004 92345678 2012000003 00000003 20120510 16:57:00
2012001005 92345678 2012000003 00000002 20120510 17:01:51
2012001003 82345678 2012000003 00000003 20120510 17:02:23
查询
同一连锁店当日同一会员卡号，在相同的终端连续2次以上（含2次）录入时间=<2分钟

------解决方案--------------------
select * from 表 a,表 b
where a.会员卡号 = b.会员卡号
and a.连锁店编号 = b.连锁店编号
and a.录入终端 = b.录入终端
and a.录入时间-b.录入时间<2/24/60
------解决方案--------------------
建表麻烦发帖可以把表先建好这样回复也效率好多
------解决方案--------------------

SQL code

create table t1 (t_no varchar2(10),t_card varchar(8),t_gh varchar2(10),t_zd varchar2(10),t_time date);

insert into t1 values ('2012001001','12345678','2012000001','00000001',to_date('20120510 16:29:23','yyyymmdd hh24:mi:ss'));
insert into t1 values ('2012001001','12345678','2012000001','00000001',to_date('20120510 16:30:44','yyyymmdd hh24:mi:ss'));
insert into t1 values ('2012001001','12345678','2012000001','00000001',to_date('20120510 16:39:12','yyyymmdd hh24:mi:ss'));
insert into t1 values ('2012001004','12345678','2012000002','00000001',to_date('20120510 16:41:21','yyyymmdd hh24:mi:ss'));
insert into t1 values ('2012001004','32345678','2012000002','00000002',to_date('20120510 16:43:56','yyyymmdd hh24:mi:ss'));
insert into t1 values ('2012001004','22345678','2012000002','00000001',to_date('20120510 16:46:49','yyyymmdd hh24:mi:ss'));
insert into t1 values ('2012001004','52345678','2012000002','00000002',to_date('20120510 16:47:23','yyyymmdd hh24:mi:ss'));
insert into t1 values ('2012001001','32345678','2012000003','00000001',to_date('20120510 16:49:10','yyyymmdd hh24:mi:ss'));
insert into t1 values ('2012001002','82345678','2012000003','00000002',to_date('20120510 16:53:08','yyyymmdd hh24:mi:ss'));
insert into t1 values ('2012001004','92345678','2012000003','00000003',to_date('20120510 16:57:00','yyyymmdd hh24:mi:ss'));
insert into t1 values ('2012001005','92345678','2012000003','00000002',to_date('20120510 17:01:51','yyyymmdd hh24:mi:ss'));
insert into t1 values ('2012001003','82345678','2012000003','00000003',to_date('20120510 17:02:23','yyyymmdd hh24:mi:ss'));
insert into t1 values ('2012001003','82345678','2012000003','00000003',to_date('20120510 17:03:55','yyyymmdd hh24:mi:ss'));
commit;

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

oracle按要求查询，高分相送解决方案

相关资料更多>

推荐阅读更多>