22,210
社区成员
发帖
与我相关
我的任务
分享如何检索一个月内连续登入的用户？
如何检索一个月内连续登入的用户？
要求：
1、只要有一次连续三天没登入就不符合要求。
2、一天内一个用户可能会有连续几次的登入。
举例loginlog表数据如下：
uid logintime
1 2007-10-1
1 2007-10-1
1 2007-10-2
1 2007-10-3
1 2007-10-4
1 2007-10-5
1 2007-10-5
1 2007-10-5
1 2007-10-7
1 2007-10-10
2 2007-10-1
2 2007-10-2
2 2007-10-4
2 2007-10-5
2 2007-10-5
2 2007-10-7
2 2007-10-9
2 2007-10-10
3 2007-10-1
3 2007-10-2
3 2007-10-3
3 2007-10-4
3 2007-10-5
3 2007-10-5
3 2007-10-8
3 2007-10-9
3 2007-10-10
4 2007-10-1
4 2007-10-5
4 2007-10-5
4 2007-10-9
4 2007-10-10
5 2007-10-1
5 2007-10-3
5 2007-10-5
5 2007-10-6
5 2007-10-10
要求输出结果uid 为：1,2,3
要求：
1、只要有一次连续三天没登入就不符合要求。
2、一天内一个用户可能会有连续几次的登入。
举例loginlog表数据如下：
uid logintime
1 2007-10-1
1 2007-10-1
1 2007-10-2
1 2007-10-3
1 2007-10-4
1 2007-10-5
1 2007-10-5
1 2007-10-5
1 2007-10-7
1 2007-10-10
2 2007-10-1
2 2007-10-2
2 2007-10-4
2 2007-10-5
2 2007-10-5
2 2007-10-7
2 2007-10-9
2 2007-10-10
3 2007-10-1
3 2007-10-2
3 2007-10-3
3 2007-10-4
3 2007-10-5
3 2007-10-5
3 2007-10-8
3 2007-10-9
3 2007-10-10
4 2007-10-1
4 2007-10-5
4 2007-10-5
4 2007-10-9
4 2007-10-10
5 2007-10-1
5 2007-10-3
5 2007-10-5
5 2007-10-6
5 2007-10-10
要求输出结果uid 为：1,2,3
...全文
请发表友善的回复…
发表回复
-狙击手- 2007-11-09
- 打赏
- 举报
晕，有点晕，
连续三天login可以这样
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)
只要有一次不符合就不算 了？
连续三天login可以这样
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)
只要有一次不符合就不算 了？
fcuandy 2007-11-08
- 打赏
- 举报
只在记录所到达的时间段中选.下面选出的是一个月内连续三天未login的
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 ')
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
dawugui 2007-11-08
- 打赏
- 举报
所以我1楼就说了五个UID都不对.
kuangdp 2007-11-08
- 打赏
- 举报
select id=identity(int,1,1),* into #temp from loginlog 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 datediff(mm,a.logintime,b.logintime)=0 and a.id+1=b.id))呵呵~~稍加引用
sp4 2007-11-08
- 打赏
- 举报
现在看看还行，不知道怎么变得懒惰了，不动手了。光在这里点评了。不要有意见。
呵呵
呵呵
sp4 2007-11-08
- 打赏
- 举报
有不严谨的地方，LZ需要一个月内。那么有时间范围限制
如果计算日期为'2007-10-14'什么的计算则仍需要修改。以上那些都是对已存数据的时间顺序计算。仍需完善
如果计算日期为'2007-10-14'什么的计算则仍需要修改。以上那些都是对已存数据的时间顺序计算。仍需完善
fcuandy 2007-11-08
- 打赏
- 举报
唉,是我看错了. 楼主是说连着三天没login..
不好意思.
不好意思.
kuangdp 2007-11-08
- 打赏
- 举报
呵呵~~开始新的生活 ,你好象理解错了吧~~~
龟龟和漫步的应该是正确的啊~
龟龟和漫步的应该是正确的啊~
sp4 2007-11-08
- 打赏
- 举报
pt1314917的不行，因为有重复的数据
大乌龟的看着应该可以（就SQL来说）
大乌龟的看着应该可以（就SQL来说）
dawugui 2007-11-08
- 打赏
- 举报
你理解错了,4,5,5,7符合楼住要求.
fcuandy 2007-11-08
- 打赏
- 举报
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 ')
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
*/
playwarcraft 2007-11-08
- 打赏
- 举报
用select distinct uid,date from tb來代替tb就可以了吧
fcuandy 2007-11-08
- 打赏
- 举报
大乌龟那个有点问题,漫步的也有问题.
uid=2,的day为 1,2,4,5,5,7,9,10 哪有连续三天的. 你们的算法把 4,5,5当成三天了.
uid=2,的day为 1,2,4,5,5,7,9,10 哪有连续三天的. 你们的算法把 4,5,5当成三天了.
pt1314917 2007-11-08
- 打赏
- 举报
借用楼上的测试数据：
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))
dawugui 2007-11-08
- 打赏
- 举报
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 ')
go
select 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 行）
*/
dawugui 2007-11-08
- 打赏
- 举报
你这五个UID都不符合吧?
