源表如下:
源表如下所示:
移动号码1 业务1 2008-05-04 失败标记2
移动号码1 业务1 2008-05-04 失败标记1
移动号码1 业务1 2008-05-04 失败标记1
移动号码1 业务1 2008-05-05 失败标记2
移动号码1 业务1 2008-05-05 失败标记2
移动号码1 业务1 2008-05-05 失败标记2
移动号码1 业务1 2008-05-07 失败标记2
移动号码1 业务1 2008-05-08 失败标记1
移动号码1 业务1 2008-05-09 失败标记1
移动号码1 业务1 2008-05-11 失败标记2
移动号码1 业务1 2008-05-12 失败标记2
移动号码1 业务1 2008-05-20 失败标记2
移动号码1 业务2 2008-05-04 失败标记2
移动号码1 业务2 2008-05-04 失败标记1
移动号码1 业务2 2008-05-04 失败标记1
移动号码1 业务2 2008-05-05 失败标记2
移动号码1 业务2 2008-05-05 失败标记2
移动号码1 业务2 2008-05-05 失败标记2
移动号码1 业务2 2008-05-07 失败标记2
移动号码1 业务3 2008-05-08 失败标记1
移动号码1 业务3 2008-05-10 失败标记1
移动号码1 业务3 2008-05-11 失败标记2
移动号码1 业务3 2008-05-12 失败标记2
移动号码1 业务3 2008-05-20 失败标记2 移动号码2 业务1 2008-05-04 失败标记2
移动号码2 业务1 2008-05-04 失败标记1
移动号码2 业务1 2008-05-04 失败标记1
移动号码2 业务1 2008-05-05 失败标记2
移动号码2 业务1 2008-05-06 失败标记2
移动号码2 业务1 2008-05-06 失败标记2
移动号码2 业务1 2008-05-08 失败标记2
移动号码2 业务1 2008-05-09 失败标记1
移动号码2 业务1 2008-05-10 失败标记1
移动号码2 业务1 2008-05-11 失败标记2
移动号码2 业务1 2008-05-12 失败标记2
移动号码2 业务1 2008-05-20 失败标记2
移动号码2 业务2 2008-05-04 失败标记2
移动号码2 业务2 2008-05-05 失败标记1
移动号码2 业务2 2008-05-05 失败标记1
移动号码2 业务2 2008-05-08 失败标记2
移动号码2 业务2 2008-05-09 失败标记2
移动号码2 业务2 2008-05-10 失败标记2
移动号码2 业务2 2008-05-11 失败标记2
移动号码2 业务3 2008-05-08 失败标记1
移动号码2 业务3 2008-05-09 失败标记1
移动号码2 业务3 2008-05-11 失败标记2
移动号码2 业务3 2008-05-12 失败标记2
移动号码2 业务3 2008-05-20 失败标记2
如果指定是连续三天包括三天以下出错的数据,那么数据应该是:
移动号码1 业务1 2008-05-07 失败标记2
移动号码1 业务1 2008-05-08 失败标记1
移动号码1 业务1 2008-05-09 失败标记1
移动号码1 业务2 2008-05-07 失败标记2
移动号码1 业务2 2008-05-07 失败标记2
移动号码1 业务2 2008-05-08 失败标记1
移动号码1 业务2 2008-05-09 失败标记1
移动号码1 业务3 2008-05-08 失败标记1
移动号码1 业务3 2008-05-09 失败标记1
移动号码1 业务3 2008-05-10 失败标记1
移动号码1 业务3 2008-05-11 失败标记2
移动号码1 业务3 2008-05-12 失败标记2
移动号码2 业务1 2008-05-04 失败标记2
移动号码2 业务1 2008-05-04 失败标记1
移动号码2 业务1 2008-05-04 失败标记1
移动号码2 业务1 2008-05-05 失败标记2
移动号码2 业务1 2008-05-06 失败标记2
移动号码2 业务1 2008-05-06 失败标记2
移动号码2 业务1 2008-05-08 失败标记2
移动号码2 业务1 2008-05-09 失败标记1
移动号码2 业务1 2008-05-10 失败标记1
移动号码2 业务1 2008-05-11 失败标记2
移动号码2 业务1 2008-05-12 失败标记2
移动号码2 业务2 2008-05-08 失败标记2
移动号码2 业务2 2008-05-09 失败标记2
移动号码2 业务2 2008-05-10 失败标记2
移动号码2 业务2 2008-05-11 失败标记2
不好意思,以前的数据搞错了点.重发一下
源表如下所示:
移动号码1 业务1 2008-05-04 失败标记2
移动号码1 业务1 2008-05-04 失败标记1
移动号码1 业务1 2008-05-04 失败标记1
移动号码1 业务1 2008-05-05 失败标记2
移动号码1 业务1 2008-05-05 失败标记2
移动号码1 业务1 2008-05-05 失败标记2
移动号码1 业务1 2008-05-07 失败标记2
移动号码1 业务1 2008-05-08 失败标记1
移动号码1 业务1 2008-05-09 失败标记1
移动号码1 业务1 2008-05-11 失败标记2
移动号码1 业务1 2008-05-12 失败标记2
移动号码1 业务1 2008-05-20 失败标记2
移动号码1 业务2 2008-05-04 失败标记2
移动号码1 业务2 2008-05-04 失败标记1
移动号码1 业务2 2008-05-04 失败标记1
移动号码1 业务2 2008-05-05 失败标记2
移动号码1 业务2 2008-05-05 失败标记2
移动号码1 业务2 2008-05-05 失败标记2
移动号码1 业务2 2008-05-07 失败标记2
移动号码1 业务3 2008-05-08 失败标记1
移动号码1 业务3 2008-05-10 失败标记1
移动号码1 业务3 2008-05-11 失败标记2
移动号码1 业务3 2008-05-12 失败标记2
移动号码1 业务3 2008-05-20 失败标记2 移动号码2 业务1 2008-05-04 失败标记2
移动号码2 业务1 2008-05-04 失败标记1
移动号码2 业务1 2008-05-04 失败标记1
移动号码2 业务1 2008-05-05 失败标记2
移动号码2 业务1 2008-05-06 失败标记2
移动号码2 业务1 2008-05-06 失败标记2
移动号码2 业务1 2008-05-08 失败标记2
移动号码2 业务1 2008-05-09 失败标记1
移动号码2 业务1 2008-05-10 失败标记1
移动号码2 业务1 2008-05-11 失败标记2
移动号码2 业务1 2008-05-12 失败标记2
移动号码2 业务1 2008-05-20 失败标记2
移动号码2 业务2 2008-05-04 失败标记2
移动号码2 业务2 2008-05-05 失败标记1
移动号码2 业务2 2008-05-05 失败标记1
移动号码2 业务2 2008-05-08 失败标记2
移动号码2 业务2 2008-05-09 失败标记2
移动号码2 业务2 2008-05-10 失败标记2
移动号码2 业务2 2008-05-11 失败标记2
移动号码2 业务3 2008-05-08 失败标记1
移动号码2 业务3 2008-05-09 失败标记1
移动号码2 业务3 2008-05-11 失败标记2
移动号码2 业务3 2008-05-12 失败标记2
移动号码2 业务3 2008-05-20 失败标记2
如果指定是连续三天包括三天以下出错的数据,那么数据应该是:
移动号码1 业务1 2008-05-07 失败标记2
移动号码1 业务1 2008-05-08 失败标记1
移动号码1 业务1 2008-05-09 失败标记1
移动号码1 业务2 2008-05-07 失败标记2
移动号码1 业务2 2008-05-07 失败标记2
移动号码1 业务2 2008-05-08 失败标记1
移动号码1 业务2 2008-05-09 失败标记1
移动号码1 业务3 2008-05-08 失败标记1
移动号码1 业务3 2008-05-09 失败标记1
移动号码1 业务3 2008-05-10 失败标记1
移动号码1 业务3 2008-05-11 失败标记2
移动号码1 业务3 2008-05-12 失败标记2
移动号码2 业务1 2008-05-04 失败标记2
移动号码2 业务1 2008-05-04 失败标记1
移动号码2 业务1 2008-05-04 失败标记1
移动号码2 业务1 2008-05-05 失败标记2
移动号码2 业务1 2008-05-06 失败标记2
移动号码2 业务1 2008-05-06 失败标记2
移动号码2 业务1 2008-05-08 失败标记2
移动号码2 业务1 2008-05-09 失败标记1
移动号码2 业务1 2008-05-10 失败标记1
移动号码2 业务1 2008-05-11 失败标记2
移动号码2 业务1 2008-05-12 失败标记2
移动号码2 业务2 2008-05-08 失败标记2
移动号码2 业务2 2008-05-09 失败标记2
移动号码2 业务2 2008-05-10 失败标记2
移动号码2 业务2 2008-05-11 失败标记2
不好意思,以前的数据搞错了点.重发一下
insert into tb select '移动号码1','业务1','2008-05-04','失败标记2'
insert into tb select '移动号码1','业务1','2008-05-04','失败标记1'
insert into tb select '移动号码1','业务1','2008-05-04','失败标记1'
insert into tb select '移动号码1','业务1','2008-05-05','失败标记2'
insert into tb select '移动号码1','业务1','2008-05-05','失败标记2'
insert into tb select '移动号码1','业务1','2008-05-05','失败标记2'
insert into tb select '移动号码1','业务1','2008-05-07','失败标记2'
insert into tb select '移动号码1','业务1','2008-05-08','失败标记1'
insert into tb select '移动号码1','业务1','2008-05-09','失败标记1'
insert into tb select '移动号码1','业务1','2008-05-11','失败标记2'
insert into tb select '移动号码1','业务1','2008-05-12','失败标记2'
insert into tb select '移动号码1','业务1','2008-05-20','失败标记2'
insert into tb select '移动号码1','业务2','2008-05-04','失败标记2'
insert into tb select '移动号码1','业务2','2008-05-04','失败标记1'
insert into tb select '移动号码1','业务2','2008-05-04','失败标记1'
insert into tb select '移动号码1','业务2','2008-05-05','失败标记2'
insert into tb select '移动号码1','业务2','2008-05-05','失败标记2'
insert into tb select '移动号码1','业务2','2008-05-05','失败标记2'
insert into tb select '移动号码1','业务2','2008-05-07','失败标记2'
insert into tb select '移动号码1','业务3','2008-05-08','失败标记1'
insert into tb select '移动号码1','业务3','2008-05-10','失败标记1'
insert into tb select '移动号码1','业务3','2008-05-11','失败标记2'
insert into tb select '移动号码1','业务3','2008-05-12','失败标记2'
insert into tb select '移动号码1','业务3','2008-05-20','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-04','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-04','失败标记1'
insert into tb select '移动号码2','业务1','2008-05-04','失败标记1'
insert into tb select '移动号码2','业务1','2008-05-05','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-06','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-06','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-08','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-09','失败标记1'
insert into tb select '移动号码2','业务1','2008-05-10','失败标记1'
insert into tb select '移动号码2','业务1','2008-05-11','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-12','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-20','失败标记2'
insert into tb select '移动号码2','业务2','2008-05-04','失败标记2'
insert into tb select '移动号码2','业务2','2008-05-05','失败标记1'
insert into tb select '移动号码2','业务2','2008-05-05','失败标记1'
insert into tb select '移动号码2','业务2','2008-05-08','失败标记2'
insert into tb select '移动号码2','业务2','2008-05-09','失败标记2'
insert into tb select '移动号码2','业务2','2008-05-10','失败标记2'
insert into tb select '移动号码2','业务2','2008-05-11','失败标记2'
insert into tb select '移动号码2','业务3','2008-05-08','失败标记1'
insert into tb select '移动号码2','业务3','2008-05-09','失败标记1'
insert into tb select '移动号码2','业务3','2008-05-11','失败标记2'
insert into tb select '移动号码2','业务3','2008-05-12','失败标记2'
insert into tb select '移动号码2','业务3','2008-05-20','失败标记2'
go
--查连续三天及以上出错的起始日期
select num,operation,dt from tb t
where not exists(select 1 from tb where datediff(day,dt,t.dt)=1 and num=t.num and operation=t.operation)
and exists(select 1 from tb where datediff(day,dt,t.dt)=-1 and num=t.num and operation=t.operation)
and exists(select 1 from tb where datediff(day,dt,t.dt)=-2 and num=t.num and operation=t.operation)
group by num,operation,dt
go
drop table tb
go
/*
num operation dt
----- --------- -----------------------
移动号码1 业务1 2008-05-07 00:00:00.000
移动号码1 业务3 2008-05-10 00:00:00.000
移动号码2 业务1 2008-05-04 00:00:00.000
移动号码2 业务1 2008-05-08 00:00:00.000
移动号码2 业务2 2008-05-08 00:00:00.000(5 行受影响)
*/
select * from table
where 时间>=N天 and haoma=号码
insert into tb select '移动号码1','业务1','2008-05-04','失败标记2'
insert into tb select '移动号码1','业务1','2008-05-04','失败标记1'
insert into tb select '移动号码1','业务1','2008-05-04','失败标记1'
insert into tb select '移动号码1','业务1','2008-05-05','失败标记2'
insert into tb select '移动号码1','业务1','2008-05-05','失败标记2'
insert into tb select '移动号码1','业务1','2008-05-05','失败标记2'
insert into tb select '移动号码1','业务1','2008-05-07','失败标记2'
insert into tb select '移动号码1','业务1','2008-05-08','失败标记1'
insert into tb select '移动号码1','业务1','2008-05-09','失败标记1'
insert into tb select '移动号码1','业务1','2008-05-11','失败标记2'
insert into tb select '移动号码1','业务1','2008-05-12','失败标记2'
insert into tb select '移动号码1','业务1','2008-05-20','失败标记2'
insert into tb select '移动号码1','业务2','2008-05-04','失败标记2'
insert into tb select '移动号码1','业务2','2008-05-04','失败标记1'
insert into tb select '移动号码1','业务2','2008-05-04','失败标记1'
insert into tb select '移动号码1','业务2','2008-05-05','失败标记2'
insert into tb select '移动号码1','业务2','2008-05-05','失败标记2'
insert into tb select '移动号码1','业务2','2008-05-05','失败标记2'
insert into tb select '移动号码1','业务2','2008-05-07','失败标记2'
insert into tb select '移动号码1','业务3','2008-05-08','失败标记1'
insert into tb select '移动号码1','业务3','2008-05-10','失败标记1'
insert into tb select '移动号码1','业务3','2008-05-11','失败标记2'
insert into tb select '移动号码1','业务3','2008-05-12','失败标记2'
insert into tb select '移动号码1','业务3','2008-05-20','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-04','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-04','失败标记1'
insert into tb select '移动号码2','业务1','2008-05-04','失败标记1'
insert into tb select '移动号码2','业务1','2008-05-05','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-06','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-06','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-08','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-09','失败标记1'
insert into tb select '移动号码2','业务1','2008-05-10','失败标记1'
insert into tb select '移动号码2','业务1','2008-05-11','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-12','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-20','失败标记2'
insert into tb select '移动号码2','业务2','2008-05-04','失败标记2'
insert into tb select '移动号码2','业务2','2008-05-05','失败标记1'
insert into tb select '移动号码2','业务2','2008-05-05','失败标记1'
insert into tb select '移动号码2','业务2','2008-05-08','失败标记2'
insert into tb select '移动号码2','业务2','2008-05-09','失败标记2'
insert into tb select '移动号码2','业务2','2008-05-10','失败标记2'
insert into tb select '移动号码2','业务2','2008-05-11','失败标记2'
insert into tb select '移动号码2','业务3','2008-05-08','失败标记1'
insert into tb select '移动号码2','业务3','2008-05-09','失败标记1'
insert into tb select '移动号码2','业务3','2008-05-11','失败标记2'
insert into tb select '移动号码2','业务3','2008-05-12','失败标记2'
insert into tb select '移动号码2','业务3','2008-05-20','失败标记2'
go
--select * from tb order by num,operation,dt
--查连续三天及以上出错的所有记录,但如果有重复的话会出错,因此最后结果消除重复.
select num,operation,dt into # from tb t
where not exists(select 1 from tb where datediff(day,dt,t.dt)=1 and num=t.num and operation=t.operation)
and exists(select 1 from tb where datediff(day,dt,t.dt)=-1 and num=t.num and operation=t.operation)
and exists(select 1 from tb where datediff(day,dt,t.dt)=-2 and num=t.num and operation=t.operation)
group by num,operation,dt
go
with tempdb(num,operation,dt) as
(select root.num,root.operation,root.dt
from tb root
where exists(select 1 from # where num=root.num and operation=root.operation and dt=root.dt)
union all
select sub.num,sub.operation,sub.dt
from tb sub,tempdb super
where sub.num=super.num and sub.operation=super.operation and datediff(day,sub.dt,super.dt)=-1
)
select * from tempdb group by num,operation,dt order by num,operation,dt
go
drop table tb
drop table #
go
/*
num operation dt
----- --------- -----------------------
移动号码1 业务1 2008-05-07 00:00:00.000
移动号码1 业务1 2008-05-08 00:00:00.000
移动号码1 业务1 2008-05-09 00:00:00.000
移动号码1 业务3 2008-05-10 00:00:00.000
移动号码1 业务3 2008-05-11 00:00:00.000
移动号码1 业务3 2008-05-12 00:00:00.000
移动号码2 业务1 2008-05-04 00:00:00.000
移动号码2 业务1 2008-05-05 00:00:00.000
移动号码2 业务1 2008-05-06 00:00:00.000
移动号码2 业务1 2008-05-08 00:00:00.000
移动号码2 业务1 2008-05-09 00:00:00.000
移动号码2 业务1 2008-05-10 00:00:00.000
移动号码2 业务1 2008-05-11 00:00:00.000
移动号码2 业务1 2008-05-12 00:00:00.000
移动号码2 业务2 2008-05-08 00:00:00.000
移动号码2 业务2 2008-05-09 00:00:00.000
移动号码2 业务2 2008-05-10 00:00:00.000
移动号码2 业务2 2008-05-11 00:00:00.000(18 行受影响)*/
qianjin036a 你是个牛人呀,佩服
insert into tb select '移动号码1','业务1','2008-05-04','失败标记2'
insert into tb select '移动号码1','业务1','2008-05-04','失败标记1'
insert into tb select '移动号码1','业务1','2008-05-04','失败标记1'
insert into tb select '移动号码1','业务1','2008-05-05','失败标记2'
insert into tb select '移动号码1','业务1','2008-05-05','失败标记2'
insert into tb select '移动号码1','业务1','2008-05-05','失败标记2'
insert into tb select '移动号码1','业务1','2008-05-07','失败标记2'
insert into tb select '移动号码1','业务1','2008-05-08','失败标记1'
insert into tb select '移动号码1','业务1','2008-05-09','失败标记1'
insert into tb select '移动号码1','业务1','2008-05-11','失败标记2'
insert into tb select '移动号码1','业务1','2008-05-12','失败标记2'
insert into tb select '移动号码1','业务1','2008-05-20','失败标记2'
insert into tb select '移动号码1','业务2','2008-05-04','失败标记2'
insert into tb select '移动号码1','业务2','2008-05-04','失败标记1'
insert into tb select '移动号码1','业务2','2008-05-04','失败标记1'
insert into tb select '移动号码1','业务2','2008-05-05','失败标记2'
insert into tb select '移动号码1','业务2','2008-05-05','失败标记2'
insert into tb select '移动号码1','业务2','2008-05-05','失败标记2'
insert into tb select '移动号码1','业务2','2008-05-07','失败标记2'
insert into tb select '移动号码1','业务3','2008-05-08','失败标记1'
insert into tb select '移动号码1','业务3','2008-05-10','失败标记1'
insert into tb select '移动号码1','业务3','2008-05-11','失败标记2'
insert into tb select '移动号码1','业务3','2008-05-12','失败标记2'
insert into tb select '移动号码1','业务3','2008-05-20','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-04','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-04','失败标记1'
insert into tb select '移动号码2','业务1','2008-05-04','失败标记1'
insert into tb select '移动号码2','业务1','2008-05-05','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-06','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-06','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-08','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-09','失败标记1'
insert into tb select '移动号码2','业务1','2008-05-10','失败标记1'
insert into tb select '移动号码2','业务1','2008-05-11','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-12','失败标记2'
insert into tb select '移动号码2','业务1','2008-05-20','失败标记2'
insert into tb select '移动号码2','业务2','2008-05-04','失败标记2'
insert into tb select '移动号码2','业务2','2008-05-05','失败标记1'
insert into tb select '移动号码2','业务2','2008-05-05','失败标记1'
insert into tb select '移动号码2','业务2','2008-05-08','失败标记2'
insert into tb select '移动号码2','业务2','2008-05-09','失败标记2'
insert into tb select '移动号码2','业务2','2008-05-10','失败标记2'
insert into tb select '移动号码2','业务2','2008-05-11','失败标记2'
insert into tb select '移动号码2','业务3','2008-05-08','失败标记1'
insert into tb select '移动号码2','业务3','2008-05-09','失败标记1'
insert into tb select '移动号码2','业务3','2008-05-11','失败标记2'
insert into tb select '移动号码2','业务3','2008-05-12','失败标记2'
insert into tb select '移动号码2','业务3','2008-05-20','失败标记2'
go
--select * from tb order by num,operation,dt
--查连续三天及以上出错的所有记录,但如果有重复的话会出错,因此最后结果消除重复.
select num,operation,dt into # from tb t
where not exists(select 1 from tb where datediff(day,dt,t.dt)=1 and num=t.num and operation=t.operation)
and exists(select 1 from tb where datediff(day,dt,t.dt)=-1 and num=t.num and operation=t.operation)
and exists(select 1 from tb where datediff(day,dt,t.dt)=-2 and num=t.num and operation=t.operation)
group by num,operation,dt
go
with tempdb(num,operation,dt) as
(select root.num,root.operation,root.dt
from tb root
where exists(select 1 from # where num=root.num and operation=root.operation and dt=root.dt)
union all
select sub.num,sub.operation,sub.dt
from tb sub,tempdb super
where sub.num=super.num and sub.operation=super.operation and datediff(day,sub.dt,super.dt)=-1
)
select a.* from tb a inner join (
select * from tempdb group by num,operation,dt
)b
on a.num=b.num and a.operation=b.operation and a.dt=b.dt
order by a.num,a.operation,a.dt
go
drop table tb
drop table #
go
/*
num operation dt errtype
----- --------- ----------------------- -------
移动号码1 业务1 2008-05-07 00:00:00.000 失败标记2
移动号码1 业务1 2008-05-08 00:00:00.000 失败标记1
移动号码1 业务1 2008-05-09 00:00:00.000 失败标记1
移动号码1 业务3 2008-05-10 00:00:00.000 失败标记1
移动号码1 业务3 2008-05-11 00:00:00.000 失败标记2
移动号码1 业务3 2008-05-12 00:00:00.000 失败标记2
移动号码2 业务1 2008-05-04 00:00:00.000 失败标记2
移动号码2 业务1 2008-05-04 00:00:00.000 失败标记1
移动号码2 业务1 2008-05-04 00:00:00.000 失败标记1
移动号码2 业务1 2008-05-05 00:00:00.000 失败标记2
移动号码2 业务1 2008-05-06 00:00:00.000 失败标记2
移动号码2 业务1 2008-05-06 00:00:00.000 失败标记2
移动号码2 业务1 2008-05-08 00:00:00.000 失败标记2
移动号码2 业务1 2008-05-09 00:00:00.000 失败标记1
移动号码2 业务1 2008-05-10 00:00:00.000 失败标记1
移动号码2 业务1 2008-05-11 00:00:00.000 失败标记2
移动号码2 业务1 2008-05-12 00:00:00.000 失败标记2
移动号码2 业务2 2008-05-08 00:00:00.000 失败标记2
移动号码2 业务2 2008-05-09 00:00:00.000 失败标记2
移动号码2 业务2 2008-05-10 00:00:00.000 失败标记2
移动号码2 业务2 2008-05-11 00:00:00.000 失败标记2(21 行受影响)*/
create table tb(num nvarchar(5),operation nvarchar(3),dt datetime,errtype nvarchar(5))
insert into tb select N'移动号码1',N'业务1',N'2008-05-04',N'失败标记2'
insert into tb select N'移动号码1',N'业务1',N'2008-05-04',N'失败标记1'
insert into tb select N'移动号码1',N'业务1',N'2008-05-04',N'失败标记1'
insert into tb select N'移动号码1',N'业务1',N'2008-05-05',N'失败标记2'
insert into tb select N'移动号码1',N'业务1',N'2008-05-05',N'失败标记2'
insert into tb select N'移动号码1',N'业务1',N'2008-05-05',N'失败标记2'
insert into tb select N'移动号码1',N'业务1',N'2008-05-07',N'失败标记2'
insert into tb select N'移动号码1',N'业务1',N'2008-05-08',N'失败标记1'
insert into tb select N'移动号码1',N'业务1',N'2008-05-09',N'失败标记1'
insert into tb select N'移动号码1',N'业务1',N'2008-05-11',N'失败标记2'
insert into tb select N'移动号码1',N'业务1',N'2008-05-12',N'失败标记2'
insert into tb select N'移动号码1',N'业务1',N'2008-05-20',N'失败标记2'
insert into tb select N'移动号码1',N'业务2',N'2008-05-04',N'失败标记2'
insert into tb select N'移动号码1',N'业务2',N'2008-05-04',N'失败标记1'
insert into tb select N'移动号码1',N'业务2',N'2008-05-04',N'失败标记1'
insert into tb select N'移动号码1',N'业务2',N'2008-05-05',N'失败标记2'
insert into tb select N'移动号码1',N'业务2',N'2008-05-05',N'失败标记2'
insert into tb select N'移动号码1',N'业务2',N'2008-05-05',N'失败标记2'
insert into tb select N'移动号码1',N'业务2',N'2008-05-07',N'失败标记2'
insert into tb select N'移动号码1',N'业务3',N'2008-05-08',N'失败标记1'
insert into tb select N'移动号码1',N'业务3',N'2008-05-10',N'失败标记1'
insert into tb select N'移动号码1',N'业务3',N'2008-05-11',N'失败标记2'
insert into tb select N'移动号码1',N'业务3',N'2008-05-12',N'失败标记2'
insert into tb select N'移动号码1',N'业务3',N'2008-05-20',N'失败标记2'
insert into tb select N'移动号码2',N'业务1',N'2008-05-04',N'失败标记2'
insert into tb select N'移动号码2',N'业务1',N'2008-05-04',N'失败标记1'
insert into tb select N'移动号码2',N'业务1',N'2008-05-04',N'失败标记1'
insert into tb select N'移动号码2',N'业务1',N'2008-05-05',N'失败标记2'
insert into tb select N'移动号码2',N'业务1',N'2008-05-06',N'失败标记2'
insert into tb select N'移动号码2',N'业务1',N'2008-05-06',N'失败标记2'
insert into tb select N'移动号码2',N'业务1',N'2008-05-08',N'失败标记2'
insert into tb select N'移动号码2',N'业务1',N'2008-05-09',N'失败标记1'
insert into tb select N'移动号码2',N'业务1',N'2008-05-10',N'失败标记1'
insert into tb select N'移动号码2',N'业务1',N'2008-05-11',N'失败标记2'
insert into tb select N'移动号码2',N'业务1',N'2008-05-12',N'失败标记2'
insert into tb select N'移动号码2',N'业务1',N'2008-05-20',N'失败标记2'
insert into tb select N'移动号码2',N'业务2',N'2008-05-04',N'失败标记2'
insert into tb select N'移动号码2',N'业务2',N'2008-05-05',N'失败标记1'
insert into tb select N'移动号码2',N'业务2',N'2008-05-05',N'失败标记1'
insert into tb select N'移动号码2',N'业务2',N'2008-05-08',N'失败标记2'
insert into tb select N'移动号码2',N'业务2',N'2008-05-09',N'失败标记2'
insert into tb select N'移动号码2',N'业务2',N'2008-05-10',N'失败标记2'
insert into tb select N'移动号码2',N'业务2',N'2008-05-11',N'失败标记2'
insert into tb select N'移动号码2',N'业务3',N'2008-05-08',N'失败标记1'
insert into tb select N'移动号码2',N'业务3',N'2008-05-09',N'失败标记1'
insert into tb select N'移动号码2',N'业务3',N'2008-05-11',N'失败标记2'
insert into tb select N'移动号码2',N'业务3',N'2008-05-12',N'失败标记2'
insert into tb select N'移动号码2',N'业务3',N'2008-05-20',N'失败标记2'go
select tt.* from tb tt join
(select distinct t1.num,t1.operation,t1.dt from
tb t1, tb t2, tb t3
where t1.num = t2.num and t2.num=t3.num
and t1.operation = t2.operation and t2.operation=t3.operation
and ((datediff(day,t1.dt,t2.dt)=-1 and datediff(day,t1.dt,t3.dt)=-2)
or (datediff(day,t1.dt,t2.dt)=1 and datediff(day,t1.dt,t3.dt)=-1)
or (datediff(day,t1.dt,t2.dt)=1 and datediff(day,t1.dt,t3.dt)=2))
) tr
on tt.num = tr.num and tt.operation=tr.operation and tt.dt = tr.dt
select a.* from tb a left join(
select distinct num,operation,dt from tb t where
( exists(select 1 from tb where num=t.num and operation=t.operation and datediff(day,dt,t.dt)=1)
and exists(select 1 from tb where num=t.num and operation=t.operation and datediff(day,dt,t.dt)=2))
or ( exists(select 1 from tb where num=t.num and operation=t.operation and datediff(day,dt,t.dt)=1)
and exists(select 1 from tb where num=t.num and operation=t.operation and datediff(day,dt,t.dt)=-1))
or ( exists(select 1 from tb where num=t.num and operation=t.operation and datediff(day,dt,t.dt)=-1)
and exists(select 1 from tb where num=t.num and operation=t.operation and datediff(day,dt,t.dt)=-2))
)b
on a.num=b.num and a.operation=b.operation and a.dt=b.dt