--创建测试数据
create table test(姓名 varchar(8),编号 int,卡号 int,日期 varchar(10),星期 int,时间 varchar(10))
insert into test values('张三',558,15609018,'2007-10-01',1,'07:52:03')
insert into test values('张三',558,15609018,'2007-10-01',1,'07:52:11')
insert into test values('张三',558,15609018,'2007-10-01',1,'07:52:16')
insert into test values('张三',558,15609018,'2007-10-01',1,'07:52:20')
insert into test values('张三',558,15609018,'2007-10-01',1,'07:52:29')
insert into test values('张三',558,15609018,'2007-10-01',1,'07:52:36')
insert into test values('张三',558,15609018,'2007-10-01',1,'18:09:39')
insert into test values('张三',558,15609018,'2007-10-01',1,'18:09:43')
insert into test values('张三',558,15609018,'2007-10-02',2,'08:44:02')
insert into test values('张三',558,15609018,'2007-10-02',2,'08:44:11')
insert into test values('张三',558,15609018,'2007-10-02',2,'08:44:16')
insert into test values('张三',558,15609018,'2007-10-02',2,'08:44:20')
insert into test values('张三',558,15609018,'2007-10-02',2,'08:44:24')
insert into test values('张三',558,15609018,'2007-10-02',2,'08:44:33')
insert into test values('张三',558,15609018,'2007-10-02',2,'08:44:37')
insert into test values('张三',558,15609018,'2007-10-02',2,'17:15:09') --执行查询语句
select
t.*
from
test t
where
not exists(select
1
from
test
where
卡号=t.卡号
and
日期=t.日期
and
datediff(ss,cast(时间 as datetime),cast(t.时间 as datetime)) between 1 and 300)--输出查询结果
/*
姓名 编号 卡号 日期 星期 时间
-------- ----------- ----------- ---------- ----------- ----------
张三 558 15609018 2007-10-01 1 07:52:03
张三 558 15609018 2007-10-01 1 18:09:39
张三 558 15609018 2007-10-02 2 08:44:02
张三 558 15609018 2007-10-02 2 17:15:09
*/--删除测试表格
drop table test
create table test(姓名 varchar(8),编号 int,卡号 int,日期 varchar(10),星期 int,时间 varchar(10))
insert into test values('张三',558,15609018,'2007-10-01',1,'07:52:03')
insert into test values('张三',558,15609018,'2007-10-01',1,'07:52:11')
insert into test values('张三',558,15609018,'2007-10-01',1,'07:52:16')
insert into test values('张三',558,15609018,'2007-10-01',1,'07:52:20')
insert into test values('张三',558,15609018,'2007-10-01',1,'07:52:29')
insert into test values('张三',558,15609018,'2007-10-01',1,'07:52:36')
insert into test values('张三',558,15609018,'2007-10-01',1,'18:09:39')
insert into test values('张三',558,15609018,'2007-10-01',1,'18:09:43')
insert into test values('张三',558,15609018,'2007-10-02',2,'08:44:02')
insert into test values('张三',558,15609018,'2007-10-02',2,'08:44:11')
insert into test values('张三',558,15609018,'2007-10-02',2,'08:44:16')
insert into test values('张三',558,15609018,'2007-10-02',2,'08:44:20')
insert into test values('张三',558,15609018,'2007-10-02',2,'08:44:24')
insert into test values('张三',558,15609018,'2007-10-02',2,'08:44:33')
insert into test values('张三',558,15609018,'2007-10-02',2,'08:44:37')
insert into test values('张三',558,15609018,'2007-10-02',2,'17:15:09') --执行查询语句
select
t.*
from
test t
where
not exists(select
1
from
test
where
卡号=t.卡号
and
日期=t.日期
and
datediff(ss,cast(时间 as datetime),cast(t.时间 as datetime)) between 1 and 300)--输出查询结果
/*
姓名 编号 卡号 日期 星期 时间
-------- ----------- ----------- ---------- ----------- ----------
张三 558 15609018 2007-10-01 1 07:52:03
张三 558 15609018 2007-10-01 1 18:09:39
张三 558 15609018 2007-10-02 2 08:44:02
张三 558 15609018 2007-10-02 2 17:15:09
*/--删除测试表格
drop table test
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货