--生成测试数据
--创建表，cno=编号，remsum=余额，ttime=时间
create table testtb(sid int identity(52,1),cno char(10),remsum varchar(10),ttime datetime)
--插入数据
insert into testtb(cno,remsum,ttime)
select '1','99','2010-03-01 05:35:28.000'
union all select '2','87','2010-03-01 06:21:28.000'
union all select '2','86','2010-03-01 06:21:31.000'
union all select '3','22','2010-03-01 06:27:20.000'
union all select '4','22','2010-03-01 06:28:20.000'
union all select '6','58','2010-03-01 07:20:20.000'
union all select '6','57','2010-03-01 07:20:22.000'
union all select '5','45','2010-03-01 07:27:20.000'
union all select '3','21','2010-03-01 12:29:20.000'
union all select '4','21','2010-03-01 13:29:20.000'
union all select '4','20','2010-03-01 13:29:23.000'
union all select '5','44','2010-03-01 14:27:20.000'
union all select '2','85','2010-03-01 16:29:20.000'
union all select '2','84','2010-03-01 16:29:25.000'
正常的情况是：同一编号（cno）一天最多只能出现两次，且每一次出现，余额(remsum)就会比上次少1，如果当天
同一编号有两次记录，那么两次记录的时间间隔不少于1分钟现出现异常数据，即某些编号同一天出现次数超过两次，或两次记录的时间间隔小于1分钟，需要查询出这些异常数据希望有两种查询结果：
一、同一编号同一天出现次数大于2，且两次记录之间的时间间隔小于1分钟
二、同一编号同一天出现次数大于2，或两次记录之间的时间间隔小于1分钟
--创建表，cno=编号，remsum=余额，ttime=时间
create table testtb(sid int identity(52,1),cno char(10),remsum varchar(10),ttime datetime)
--插入数据
insert into testtb(cno,remsum,ttime)
select '1','99','2010-03-01 05:35:28.000'
union all select '2','87','2010-03-01 06:21:28.000'
union all select '2','86','2010-03-01 06:21:31.000'
union all select '3','22','2010-03-01 06:27:20.000'
union all select '4','22','2010-03-01 06:28:20.000'
union all select '6','58','2010-03-01 07:20:20.000'
union all select '6','57','2010-03-01 07:20:22.000'
union all select '5','45','2010-03-01 07:27:20.000'
union all select '3','21','2010-03-01 12:29:20.000'
union all select '4','21','2010-03-01 13:29:20.000'
union all select '4','20','2010-03-01 13:29:23.000'
union all select '5','44','2010-03-01 14:27:20.000'
union all select '2','85','2010-03-01 16:29:20.000'
union all select '2','84','2010-03-01 16:29:25.000'
正常的情况是：同一编号（cno）一天最多只能出现两次，且每一次出现，余额(remsum)就会比上次少1，如果当天
同一编号有两次记录，那么两次记录的时间间隔不少于1分钟现出现异常数据，即某些编号同一天出现次数超过两次，或两次记录的时间间隔小于1分钟，需要查询出这些异常数据希望有两种查询结果：
一、同一编号同一天出现次数大于2，且两次记录之间的时间间隔小于1分钟
二、同一编号同一天出现次数大于2，或两次记录之间的时间间隔小于1分钟
--创建表，cno=编号，remsum=余额，ttime=时间
create table testtb(sid int identity(52,1),cno char(10),remsum varchar(10),ttime datetime)
--插入数据
insert into testtb(cno,remsum,ttime)
select '1','99','2010-03-01 05:35:28.000'
union all select '2','87','2010-03-01 06:21:28.000'
union all select '2','86','2010-03-01 06:21:31.000'
union all select '3','22','2010-03-01 06:27:20.000'
union all select '4','22','2010-03-01 06:28:20.000'
union all select '6','58','2010-03-01 07:20:20.000'
union all select '6','57','2010-03-01 07:20:22.000'
union all select '5','45','2010-03-01 07:27:20.000'
union all select '3','21','2010-03-01 12:29:20.000'
union all select '4','21','2010-03-01 13:29:20.000'
union all select '4','20','2010-03-01 13:29:23.000'
union all select '5','44','2010-03-01 14:27:20.000'
union all select '2','85','2010-03-01 16:29:20.000'
union all select '2','84','2010-03-01 16:29:25.000'
select *,
(select count(1) from testtb where cno=t.cno and datediff(dd,ttime,t.ttime)=0 and sid<=t.sid) num into #t
from testtb t select * from #t where num>2sid cno remsum ttime num
----------- ---------- ---------- ----------------------- -----------
62 4 20 2010-03-01 13:29:23.000 3
64 2 85 2010-03-01 16:29:20.000 3
65 2 84 2010-03-01 16:29:25.000 4(3 行受影响)
--创建表，cno=编号，remsum=余额，ttime=时间
create table testtb(sid int identity(52,1),cno char(10),remsum varchar(10),ttime datetime)
--插入数据
insert into testtb(cno,remsum,ttime)
select '1','99','2010-03-01 05:35:28.000'
union all select '2','87','2010-03-01 06:21:28.000'
union all select '2','86','2010-03-01 06:21:31.000'
union all select '3','22','2010-03-01 06:27:20.000'
union all select '4','22','2010-03-01 06:28:20.000'
union all select '6','58','2010-03-01 07:20:20.000'
union all select '6','57','2010-03-01 07:20:22.000'
union all select '5','45','2010-03-01 07:27:20.000'
union all select '3','21','2010-03-01 12:29:20.000'
union all select '4','21','2010-03-01 13:29:20.000'
union all select '4','20','2010-03-01 13:29:23.000'
union all select '5','44','2010-03-01 14:27:20.000'
union all select '2','85','2010-03-01 16:29:20.000'
union all select '2','84','2010-03-01 16:29:25.000'
select *,
(select count(1) from testtb where cno=t.cno and datediff(dd,ttime,t.ttime)=0 and sid<=t.sid) num into #t
from testtb t select * from testtb T
where cno IN(
select cno from #t where num>2)
AND EXISTS(SELECT 1 FROM TESTTB WHERE CNO=T.CNO AND ABS(DATEDIFF(MI,TTIME,T.TTIME))<1)
sid cno remsum ttime
----------- ---------- ---------- -----------------------
53 2 87 2010-03-01 06:21:28.000
54 2 86 2010-03-01 06:21:31.000
56 4 22 2010-03-01 06:28:20.000
61 4 21 2010-03-01 13:29:20.000
62 4 20 2010-03-01 13:29:23.000
64 2 85 2010-03-01 16:29:20.000
65 2 84 2010-03-01 16:29:25.000(7 行受影响)
from testtb t
where exists(
select 1 from testtb
where cno=t.cno and convert(char,ttime,23)=convert(char,t.ttime,23)
having count(1)>2)
--创建表，cno=编号，remsum=余额，ttime=时间
create table testtb(sid int identity(52,1),cno char(10),remsum varchar(10),ttime datetime)
--插入数据
insert into testtb(cno,remsum,ttime)
select '1','99','2010-03-01 05:35:28.000'
union all select '2','87','2010-03-01 06:21:28.000'
union all select '2','86','2010-03-01 06:21:31.000'
union all select '3','22','2010-03-01 06:27:20.000'
union all select '4','22','2010-03-01 06:28:20.000'
union all select '6','58','2010-03-01 07:20:20.000'
union all select '6','57','2010-03-01 07:20:22.000'
union all select '5','45','2010-03-01 07:27:20.000'
union all select '3','21','2010-03-01 12:29:20.000'
union all select '4','21','2010-03-01 13:29:20.000'
union all select '4','20','2010-03-01 13:29:23.000'
union all select '5','44','2010-03-01 14:27:20.000'
union all select '2','85','2010-03-01 16:29:20.000'
union all select '2','84','2010-03-01 16:29:25.000';with t as(
select *
from testtb t
where exists(
select 1 from testtb
where cno=t.cno and convert(char,ttime,23)=convert(char,t.ttime,23)
having count(1)>2)
)
select * from t a
where exists(select 1 from t where abs(datediff(ss,ttime,a.ttime))<60)
/*
sid cno remsum ttime
----------- ---------- ---------- -----------------------
53 2 87 2010-03-01 06:21:28.000
54 2 86 2010-03-01 06:21:31.000
56 4 22 2010-03-01 06:28:20.000
61 4 21 2010-03-01 13:29:20.000
62 4 20 2010-03-01 13:29:23.000
64 2 85 2010-03-01 16:29:20.000
65 2 84 2010-03-01 16:29:25.000(7 行受影响)
*/
--插入数据
insert into testtb(cno,remsum,ttime)
select '1','99','2010-03-01 05:35:28.000'
union all select '2','87','2010-03-01 06:21:28.000'
union all select '2','86','2010-03-01 06:21:31.000'
union all select '3','22','2010-03-01 06:27:20.000'
union all select '4','22','2010-03-01 06:28:20.000'
union all select '6','58','2010-03-01 07:20:20.000'
union all select '6','57','2010-03-01 07:20:22.000'
union all select '5','45','2010-03-01 07:27:20.000'
union all select '3','21','2010-03-01 12:29:20.000'
union all select '4','21','2010-03-01 13:29:20.000'
union all select '4','20','2010-03-01 13:29:23.000'
union all select '5','44','2010-03-01 14:27:20.000'
union all select '2','85','2010-03-01 16:29:20.000'
union all select '2','84','2010-03-01 16:29:25.000'select m.* from testtb m where exists (select 1 from
(select cno , convert(varchar(10),ttime,120) ttime from testtb group by cno , convert(varchar(10),ttime,120) having count(1) > 2) n
where n.cno = m.cno and n.ttime = convert(varchar(10),m.ttime,120))
order by m.cno , m.ttimedrop table testtb/*
sid cno remsum ttime
----------- ---------- ---------- ------------------------------------------------------
53 2 87 2010-03-01 06:21:28.000
54 2 86 2010-03-01 06:21:31.000
64 2 85 2010-03-01 16:29:20.000
65 2 84 2010-03-01 16:29:25.000
56 4 22 2010-03-01 06:28:20.000
61 4 21 2010-03-01 13:29:20.000
62 4 20 2010-03-01 13:29:23.000（所影响的行数为 7 行）*/
--创建表，cno=编号，remsum=余额，ttime=时间
create table testtb(sid int identity(52,1),cno char(10),remsum varchar(10),ttime datetime)
--插入数据
insert into testtb(cno,remsum,ttime)
select '1','99','2010-03-01 05:35:28.000'
union all select '2','87','2010-03-01 06:21:28.000'
union all select '2','86','2010-03-01 06:21:31.000'
union all select '3','22','2010-03-01 06:27:20.000'
union all select '4','22','2010-03-01 06:28:20.000'
union all select '6','58','2010-03-01 07:20:20.000'
union all select '6','57','2010-03-01 07:20:22.000'
union all select '5','45','2010-03-01 07:27:20.000'
union all select '3','21','2010-03-01 12:29:20.000'
union all select '4','21','2010-03-01 13:29:20.000'
union all select '4','20','2010-03-01 13:29:23.000'
union all select '5','44','2010-03-01 14:27:20.000'
union all select '2','85','2010-03-01 16:29:20.000'
union all select '2','84','2010-03-01 16:29:25.000'select
*
from
(
select *, (select count(1) from testtb where cno=t.cno and datediff(dd,ttime,t.ttime)=0 and sid<=t.sid) as num from testtb t
)t
where num>2drop table testtb
/*sid cno remsum ttime num
----------- ---------- ---------- ----------------------- -----------
62 4 20 2010-03-01 13:29:23.000 3
64 2 85 2010-03-01 16:29:20.000 3
65 2 84 2010-03-01 16:29:25.000 4(3 行受影响)*/
exists(select 1 from testtb where cno=a.cno group by cno having count(*)>2)
or exists(select 1 from testtb where cno=a.cno and a.sid<>sid and abs(datediff(ms,ttime,a.ttime))<60) sid cno remsum ttime
----------- ---------- ---------- -----------------------
53 2 87 2010-03-01 06:21:28.000
54 2 86 2010-03-01 06:21:31.000
56 4 22 2010-03-01 06:28:20.000
61 4 21 2010-03-01 13:29:20.000
62 4 20 2010-03-01 13:29:23.000
64 2 85 2010-03-01 16:29:20.000
65 2 84 2010-03-01 16:29:25.000(7 行受影响)
select
*
from
testtb a
where
exists(select 1 from testtb where cno=a.cno group by cno having count(*)>2)
or
exists(select 1 from testtb where cno=a.cno and a.sid<>sid and abs(datediff(ms,ttime,a.ttime))<60
--一、同一编号同一天出现次数大于2，且两次记录之间的时间间隔小于1分钟
select *
from
testtb a
where
exists(select 1 from testtb where cno = a.cno and ttime <> a.ttime and abs(datediff(ss,ttime,a.ttime))<60)
order by
cno
--结果
/*
53 2 87 2010-03-01 06:21:28.000
54 2 86 2010-03-01 06:21:31.000
64 2 85 2010-03-01 16:29:20.000
65 2 84 2010-03-01 16:29:25.000
61 4 21 2010-03-01 13:29:20.000
62 4 20 2010-03-01 13:29:23.000
57 6 58 2010-03-01 07:20:20.000
58 6 57 2010-03-01 07:20:22.000
*/
select *
from testtb t
where exists(
select 1 from testtb
where cno=t.cno and convert(char,ttime,23)=convert(char,t.ttime,23)
having count(1)>2)
)
select * from t a
where exists(select 1 from t where cno=a.cno and abs(datediff(ss,ttime,a.ttime))<60)
/*
sid cno remsum ttime
----------- ---------- ---------- -----------------------
53 2 87 2010-03-01 06:21:28.000
54 2 86 2010-03-01 06:21:31.000
56 4 22 2010-03-01 06:28:20.000
61 4 21 2010-03-01 13:29:20.000
62 4 20 2010-03-01 13:29:23.000
64 2 85 2010-03-01 16:29:20.000
65 2 84 2010-03-01 16:29:25.000(7 行受影响)
*/5#手快漏了个条件cno=a.cno 补上
写错了select * from testtb a where
exists(select 1 from testtb where cno=a.cno group by cno having count(*)>2)
or
(exists(select 1 from testtb where cno=a.cno group by cno having count(*)=2)
and
exists(select 1 from testtb where cno=a.cno and a.sid<>sid and abs(datediff(ss,ttime,a.ttime))<60)
)
sid cno remsum ttime
----------- ---------- ---------- -----------------------
53 2 87 2010-03-01 06:21:28.000
54 2 86 2010-03-01 06:21:31.000
56 4 22 2010-03-01 06:28:20.000
57 6 58 2010-03-01 07:20:20.000
58 6 57 2010-03-01 07:20:22.000
61 4 21 2010-03-01 13:29:20.000
62 4 20 2010-03-01 13:29:23.000
64 2 85 2010-03-01 16:29:20.000
65 2 84 2010-03-01 16:29:25.000(9 行受影响)
create table testtb(sid int identity(52,1),cno char(10),remsum varchar(10),ttime datetime)
--插入数据
insert into testtb(cno,remsum,ttime)
select '1','99','2010-03-01 05:35:28.000'
union all select '2','87','2010-03-01 06:21:28.000'
union all select '2','86','2010-03-01 06:21:31.000'
union all select '3','22','2010-03-01 06:27:20.000'
union all select '4','22','2010-03-01 06:28:20.000'
union all select '6','58','2010-03-01 07:20:20.000'
union all select '6','57','2010-03-01 07:20:22.000'
union all select '5','45','2010-03-01 07:27:20.000'
union all select '3','21','2010-03-01 12:29:20.000'
union all select '4','21','2010-03-01 13:29:20.000'
union all select '4','20','2010-03-01 13:29:23.000'
union all select '5','44','2010-03-01 14:27:20.000'
union all select '2','85','2010-03-01 16:29:20.000'
union all select '2','84','2010-03-01 16:29:25.000'
go
select m.* from testtb m where exists (select 1 from
(select cno , convert(varchar(10),ttime,120) ttime from testtb group by cno , convert(varchar(10),ttime,120) having count(1) > 2) n
where n.cno = m.cno and n.ttime = convert(varchar(10),m.ttime,120))
union all
select m.* from testtb m , testtb n
where m.cno = n.cno and convert(varchar(10),m.ttime,120) = convert(varchar(10),n.ttime,120) and m.ttime < n.ttime and datediff(ss,m.ttime,n.ttime) <= 60
and not exists (select 1 from
(select cno , convert(varchar(10),ttime,120) ttime from testtb group by cno , convert(varchar(10),ttime,120) having count(1) > 2) t
where t.cno = m.cno and t.ttime = convert(varchar(10),m.ttime,120))
order by cno , ttimedrop table testtb/*
sid cno remsum ttime
----------- ---------- ---------- ------------------------------------------------------
53 2 87 2010-03-01 06:21:28.000
54 2 86 2010-03-01 06:21:31.000
64 2 85 2010-03-01 16:29:20.000
65 2 84 2010-03-01 16:29:25.000
56 4 22 2010-03-01 06:28:20.000
61 4 21 2010-03-01 13:29:20.000
62 4 20 2010-03-01 13:29:23.000
57 6 58 2010-03-01 07:20:20.000（所影响的行数为 8 行）*/
--插入数据
insert into testtb(cno,remsum,ttime)
select '1','99','2010-03-01 05:35:28.000'
union all select '2','87','2010-03-01 06:21:28.000'
union all select '2','86','2010-03-01 06:21:31.000'
union all select '3','22','2010-03-01 06:27:20.000'
union all select '4','22','2010-03-01 06:28:20.000'
union all select '6','58','2010-03-01 07:20:20.000'
union all select '6','57','2010-03-01 07:20:22.000'
union all select '5','45','2010-03-01 07:27:20.000'
union all select '3','21','2010-03-01 12:29:20.000'
union all select '4','21','2010-03-01 13:29:20.000'
union all select '4','20','2010-03-01 13:29:23.000'
union all select '5','44','2010-03-01 14:27:20.000'
union all select '2','85','2010-03-01 16:29:20.000'
union all select '2','84','2010-03-01 16:29:25.000'
go
select m.* from testtb m where exists (select 1 from
(select cno , convert(varchar(10),ttime,120) ttime from testtb group by cno , convert(varchar(10),ttime,120) having count(1) > 2) n
where n.cno = m.cno and n.ttime = convert(varchar(10),m.ttime,120))
union all
select m.* from testtb m , testtb n
where m.cno = n.cno and convert(varchar(10),m.ttime,120) = convert(varchar(10),n.ttime,120) and m.ttime <> n.ttime and abs(datediff(ss,m.ttime,n.ttime)) <= 60
and not exists (select 1 from
(select cno , convert(varchar(10),ttime,120) ttime from testtb group by cno , convert(varchar(10),ttime,120) having count(1) > 2) t
where t.cno = m.cno and t.ttime = convert(varchar(10),m.ttime,120))
order by cno , ttimedrop table testtb/*
sid cno remsum ttime
----------- ---------- ---------- ------------------------------------------------------
53 2 87 2010-03-01 06:21:28.000
54 2 86 2010-03-01 06:21:31.000
64 2 85 2010-03-01 16:29:20.000
65 2 84 2010-03-01 16:29:25.000
56 4 22 2010-03-01 06:28:20.000
61 4 21 2010-03-01 13:29:20.000
62 4 20 2010-03-01 13:29:23.000
57 6 58 2010-03-01 07:20:20.000
58 6 57 2010-03-01 07:20:22.000（所影响的行数为 9 行）
*/
--插入数据
insert into testtb(cno,remsum,ttime)
select '1','99','2010-03-01 05:35:28.000'
union all select '2','87','2010-03-01 06:21:28.000'
union all select '2','86','2010-03-01 06:21:31.000'
union all select '3','22','2010-03-01 06:27:20.000'
union all select '4','22','2010-03-01 06:28:20.000'
union all select '6','58','2010-03-01 07:20:20.000'
union all select '6','57','2010-03-01 07:20:22.000'
union all select '5','45','2010-03-01 07:27:20.000'
union all select '3','21','2010-03-01 12:29:20.000'
union all select '4','21','2010-03-01 13:29:20.000'
union all select '4','20','2010-03-01 13:29:23.000'
union all select '5','44','2010-03-01 14:27:20.000'
union all select '2','85','2010-03-01 16:29:20.000'
union all select '2','84','2010-03-01 16:29:25.000'
go
select m.* from testtb m where exists (select 1 from
(select cno , convert(varchar(10),ttime,120) ttime from testtb group by cno , convert(varchar(10),ttime,120) having count(1) > 2) n
where n.cno = m.cno and n.ttime = convert(varchar(10),m.ttime,120))
union all
select m.* from testtb m , testtb n
where m.cno = n.cno and convert(varchar(10),m.ttime,120) = convert(varchar(10),n.ttime,120) and m.ttime <> n.ttime and abs(datediff(ss,m.ttime,n.ttime)) <= 60
and exists (select 1 from
(select cno , convert(varchar(10),ttime,120) ttime from testtb group by cno , convert(varchar(10),ttime,120) having count(1) = 2) t
where t.cno = m.cno and t.ttime = convert(varchar(10),m.ttime,120))
order by cno , ttimedrop table testtb/*
sid cno remsum ttime
----------- ---------- ---------- ------------------------------------------------------
53 2 87 2010-03-01 06:21:28.000
54 2 86 2010-03-01 06:21:31.000
64 2 85 2010-03-01 16:29:20.000
65 2 84 2010-03-01 16:29:25.000
56 4 22 2010-03-01 06:28:20.000
61 4 21 2010-03-01 13:29:20.000
62 4 20 2010-03-01 13:29:23.000
57 6 58 2010-03-01 07:20:20.000
58 6 57 2010-03-01 07:20:22.000（所影响的行数为 9 行）*/
create table testtb(sid int identity(52,1),cno char(10),remsum varchar(10),ttime datetime)
--插入数据
insert into testtb(cno,remsum,ttime)
select '1','99','2010-03-01 05:35:28.000'
union all select '2','87','2010-03-01 06:21:28.000'
union all select '2','86','2010-03-01 06:21:31.000'
union all select '3','22','2010-03-01 06:27:20.000'
union all select '4','22','2010-03-01 06:28:20.000'
union all select '6','58','2010-03-01 07:20:20.000'
union all select '6','57','2010-03-01 07:20:22.000'
union all select '5','45','2010-03-01 07:27:20.000'
union all select '3','21','2010-03-01 12:29:20.000'
union all select '4','21','2010-03-01 13:29:20.000'
union all select '4','20','2010-03-01 13:29:23.000'
union all select '5','44','2010-03-01 14:27:20.000'
union all select '2','85','2010-03-01 16:29:20.000'
union all select '2','84','2010-03-01 16:29:25.000'
goselect t1.* from testtb t1 ,
(
select distinct cno , ttime from
(
select t.cno , convert(varchar(10),t.ttime,120) ttime, s = datediff(s ,ttime, (select top 1 ttime from testtb where cno = t.cno and convert(varchar(10),ttime,120) = convert(varchar(10),t.ttime,120) and ttime > t.ttime order by ttime)) from testtb t
) m where s <= 60
) t2
where t1.cno = t2.cno and convert(varchar(10),t1.ttime,120) = t2.ttime
order by t1.cno , t1.ttimedrop table testtb/*
sid cno remsum ttime
----------- ---------- ---------- ------------------------------------------------------
53 2 87 2010-03-01 06:21:28.000
54 2 86 2010-03-01 06:21:31.000
64 2 85 2010-03-01 16:29:20.000
65 2 84 2010-03-01 16:29:25.000
56 4 22 2010-03-01 06:28:20.000
61 4 21 2010-03-01 13:29:20.000
62 4 20 2010-03-01 13:29:23.000
57 6 58 2010-03-01 07:20:20.000
58 6 57 2010-03-01 07:20:22.000（所影响的行数为 9 行）*/