create table tb (uid int,logintime datetime)
insert into tb values(1, '2007-10-1')
insert into tb values(1, '2007-10-1')
insert into tb values(1, '2007-10-2')
insert into tb values(1, '2007-10-3 ')
insert into tb values(1, '2007-10-4 ')
insert into tb values(1, '2007-10-5 ')
insert into tb values(1, '2007-10-5 ')
insert into tb values(1, '2007-10-5 ')
insert into tb values(1, '2007-10-7 ')
insert into tb values(1, '2007-10-10 ')
insert into tb values(2, '2007-10-1 ')
insert into tb values(2, '2007-10-2 ')
insert into tb values(2, '2007-10-4 ')
insert into tb values(2, '2007-10-5 ')
insert into tb values(2, '2007-10-5 ')
insert into tb values(2, '2007-10-7 ')
insert into tb values(2, '2007-10-9 ')
insert into tb values(2, '2007-10-10 ')
insert into tb values(3, '2007-10-1 ')
insert into tb values(3, '2007-10-2 ')
insert into tb values(3, '2007-10-3 ')
insert into tb values(3, '2007-10-4 ')
insert into tb values(3, '2007-10-5 ')
insert into tb values(3, '2007-10-5 ')
insert into tb values(3, '2007-10-8 ')
insert into tb values(3, '2007-10-9 ')
insert into tb values(3, '2007-10-10 ')
insert into tb values(4, '2007-10-1 ')
insert into tb values(4, '2007-10-5 ')
insert into tb values(4, '2007-10-5 ')
insert into tb values(4, '2007-10-9 ')
insert into tb values(4, '2007-10-10 ')
insert into tb values(5, '2007-10-1 ')
insert into tb values(5, '2007-10-3 ')
insert into tb values(5, '2007-10-5 ')
insert into tb values(5, '2007-10-6 ')
insert into tb values(5, '2007-10-10 ')
goselect distinct uid from tb where uid not in
(
select distinct t1.uid from
(select px=(select count(1) from (select distinct * from tb) t where uid=a.uid and logintime<a.logintime)+1 , * from (select distinct * from tb) a) t1,
(select px=(select count(1) from (select distinct * from tb) t where uid=a.uid and logintime<a.logintime)+1 , * from (select distinct * from tb) a) t2
where t1.uid = t2.uid and t1.px = t2.px - 1 and datediff(day,t1.logintime,t2.logintime) > 3
)
drop table tb/*
uid
-----------
1
2
3(所影响的行数为 3 行)
*/
insert into tb values(1, '2007-10-1')
insert into tb values(1, '2007-10-1')
insert into tb values(1, '2007-10-2')
insert into tb values(1, '2007-10-3 ')
insert into tb values(1, '2007-10-4 ')
insert into tb values(1, '2007-10-5 ')
insert into tb values(1, '2007-10-5 ')
insert into tb values(1, '2007-10-5 ')
insert into tb values(1, '2007-10-7 ')
insert into tb values(1, '2007-10-10 ')
insert into tb values(2, '2007-10-1 ')
insert into tb values(2, '2007-10-2 ')
insert into tb values(2, '2007-10-4 ')
insert into tb values(2, '2007-10-5 ')
insert into tb values(2, '2007-10-5 ')
insert into tb values(2, '2007-10-7 ')
insert into tb values(2, '2007-10-9 ')
insert into tb values(2, '2007-10-10 ')
insert into tb values(3, '2007-10-1 ')
insert into tb values(3, '2007-10-2 ')
insert into tb values(3, '2007-10-3 ')
insert into tb values(3, '2007-10-4 ')
insert into tb values(3, '2007-10-5 ')
insert into tb values(3, '2007-10-5 ')
insert into tb values(3, '2007-10-8 ')
insert into tb values(3, '2007-10-9 ')
insert into tb values(3, '2007-10-10 ')
insert into tb values(4, '2007-10-1 ')
insert into tb values(4, '2007-10-5 ')
insert into tb values(4, '2007-10-5 ')
insert into tb values(4, '2007-10-9 ')
insert into tb values(4, '2007-10-10 ')
insert into tb values(5, '2007-10-1 ')
insert into tb values(5, '2007-10-3 ')
insert into tb values(5, '2007-10-5 ')
insert into tb values(5, '2007-10-6 ')
insert into tb values(5, '2007-10-10 ')
goselect distinct uid from tb where uid not in
(
select distinct t1.uid from
(select px=(select count(1) from (select distinct * from tb) t where uid=a.uid and logintime<a.logintime)+1 , * from (select distinct * from tb) a) t1,
(select px=(select count(1) from (select distinct * from tb) t where uid=a.uid and logintime<a.logintime)+1 , * from (select distinct * from tb) a) t2
where t1.uid = t2.uid and t1.px = t2.px - 1 and datediff(day,t1.logintime,t2.logintime) > 3
)
drop table tb/*
uid
-----------
1
2
3(所影响的行数为 3 行)
*/
借用楼上的测试数据:select id=identity(int,1,1),* into #temp from tb group by uid,logintime order by uid,logintime
select distinct uid from #temp where uid not in(
select uid from #temp where id not in
(select a.id from #temp a,#temp b where datediff(dd,a.logintime,b.logintime)<=3 and a.id+1=b.id))
uid=2,的day为 1,2,4,5,5,7,9,10 哪有连续三天的. 你们的算法把 4,5,5当成三天了.
insert into tb values(1, '2007-10-1')
insert into tb values(1, '2007-10-1')
insert into tb values(1, '2007-10-2')
insert into tb values(1, '2007-10-3 ')
insert into tb values(1, '2007-10-4 ')
insert into tb values(1, '2007-10-5 ')
insert into tb values(1, '2007-10-5 ')
insert into tb values(1, '2007-10-5 ')
insert into tb values(1, '2007-10-7 ')
insert into tb values(1, '2007-10-10 ')
insert into tb values(2, '2007-10-1 ')
insert into tb values(2, '2007-10-2 ')
insert into tb values(2, '2007-10-4 ')
insert into tb values(2, '2007-10-5 ')
insert into tb values(2, '2007-10-5 ')
insert into tb values(2, '2007-10-7 ')
insert into tb values(2, '2007-10-9 ')
insert into tb values(2, '2007-10-10 ')
insert into tb values(3, '2007-10-1 ')
insert into tb values(3, '2007-10-2 ')
insert into tb values(3, '2007-10-3 ')
insert into tb values(3, '2007-10-4 ')
insert into tb values(3, '2007-10-5 ')
insert into tb values(3, '2007-10-5 ')
insert into tb values(3, '2007-10-8 ')
insert into tb values(3, '2007-10-9 ')
insert into tb values(3, '2007-10-10 ')
insert into tb values(4, '2007-10-1 ')
insert into tb values(4, '2007-10-5 ')
insert into tb values(4, '2007-10-5 ')
insert into tb values(4, '2007-10-9 ')
insert into tb values(4, '2007-10-10 ')
insert into tb values(5, '2007-10-1 ')
insert into tb values(5, '2007-10-3 ')
insert into tb values(5, '2007-10-5 ')
insert into tb values(5, '2007-10-6 ')
insert into tb values(5, '2007-10-10 ')select distinct uid from tb a
where exists(select 1 from tb where uid=a.uid and datediff(dd,logintime,a.logintime)=1 and datediff(mm,logintime,a.logintime)=0)
and exists(select 1 from tb where uid=a.uid and datediff(dd,logintime,a.logintime)=-1 and datediff(mm,logintime,a.logintime)=0)
DROP TABLE tb
/*
1
3
*/
不好意思.
select distinct uid from #temp where uid not in(
select uid from #temp where id not in
(select a.id from #temp a,#temp b where datediff(dd,a.logintime,b.logintime)<=3 and datediff(mm,a.logintime,b.logintime)=0 and a.id+1=b.id))呵呵~~稍加引用
insert into tb values(1, '2007-10-1')
insert into tb values(1, '2007-10-1')
insert into tb values(1, '2007-10-2')
insert into tb values(1, '2007-10-3 ')
insert into tb values(1, '2007-10-4 ')
insert into tb values(1, '2007-10-5 ')
insert into tb values(1, '2007-10-5 ')
insert into tb values(1, '2007-10-5 ')
insert into tb values(1, '2007-10-7 ')
insert into tb values(1, '2007-10-10 ')
insert into tb values(2, '2007-10-1 ')
insert into tb values(2, '2007-10-2 ')
insert into tb values(2, '2007-10-4 ')
insert into tb values(2, '2007-10-5 ')
insert into tb values(2, '2007-10-5 ')
insert into tb values(2, '2007-10-7 ')
insert into tb values(2, '2007-10-9 ')
insert into tb values(2, '2007-10-10 ')
insert into tb values(3, '2007-10-1 ')
insert into tb values(3, '2007-10-2 ')
insert into tb values(3, '2007-10-3 ')
insert into tb values(3, '2007-10-4 ')
insert into tb values(3, '2007-10-5 ')
insert into tb values(3, '2007-10-5 ')
insert into tb values(3, '2007-10-8 ')
insert into tb values(3, '2007-10-9 ')
insert into tb values(3, '2007-10-10 ')
insert into tb values(4, '2007-10-1 ')
insert into tb values(4, '2007-10-5 ')
insert into tb values(4, '2007-10-5 ')
insert into tb values(4, '2007-10-9 ')
insert into tb values(4, '2007-10-10 ')
insert into tb values(5, '2007-10-1 ')
insert into tb values(5, '2007-10-3 ')
insert into tb values(5, '2007-10-5 ')
insert into tb values(5, '2007-10-6 ')
insert into tb values(5, '2007-10-10 ')select distinct a.uid from tb a
inner join tb b
on datediff(mm,a.logintime,b.logintime)=0 and datediff(dd,a.logintime,b.logintime)>=4 and a.uid=b.uid
where not exists(select 1 from tb where logintime between dateadd(dd,1,a.logintime) and dateadd(dd,-1,b.logintime) and uid=a.uid)
DROP TABLE tb
select distinct A.uid from tb A,tb B,
(select 1 as N
union all select 2
union all select 3
) C
where A.uid=B.uid and B.logintime=dateadd(d,N,A.logintime)group by A.uid,A.logintime
having(count(*)>3)
只要有一次不符合就不算 了?