如何检索一个月内连续登入的用户？

[程式人生2000]

如何检索一个月内连续登入的用户？

要求：
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

[懒得去死]

select distinct a.uid from loginlog a,loginlog b ,loginlog c where
a.logintime+1 = b.logintime
 and
a.logintime+2 = c.logintime
 and
a.uid = b.uid 
and 
a.uid = c.uid;

[懒得去死]

还有,按照LZ的说法,结果中应该只有1和3 ,没有2 阿

[懒得去死]

select distinct a.uid from loginlog a,loginlog b ,loginlog c 
where
date_add(a.logintime,interval 1 day) = b.logintime
 and
date_add(a.logintime,interval 2 day) = c.logintime
 and
a.uid = b.uid 
and 
a.uid = c.uid;

[yxh007]

楼上的,'loginlog'是什么啊? 'logintime'呢?

[yxh007]

明白了,是表和字段名,可以显示出来只有1,3 没有2啊

[懒得去死]

要求：
1、只要有一次连续三天没登入就不符合要求。
2、一天内一个用户可能会有连续几次的登入。


按照 你的要求不会有2出现的。

[lijiaxiang]

楼主说的是连续三天，如6,7,8三天都没有登录才不符合要求，而3，6，8三天是不连续的，故2算是连续登录了。


MYSQL没用过，不知道语法如何，给一段ACCESS的查询代码楼主试试：
SELECT loginlog.uid
FROM loginlog
GROUP BY loginlog.uid
HAVING (((Max(DateDiff("d",IIf(IsNull(DMax("[logintime]","loginlog","[uid]=" & [uid] & " and [logintime]<#" & [logintime] & "#")),"2007-10-1",DMax("[logintime]","loginlog","[uid]=" & [uid] & " and [logintime]<#" & [logintime] & "#")),[logintime])))<4));


注意，代码中指定有本月最小日期为"2007-10-1",楼主应按实际需要设置此值，另外，如果是最后三天没登录，只是这样也是不能反映出来的，因为不知楼主是否说这是在今日之前的一个月，这里也不妄作判断了。

[lijiaxiang]

SELECT loginlog.uid
FROM loginlog
GROUP BY loginlog.uid
HAVING (((Max((DateDiff("d",[logintime],IIf(IsNull(DMin("[logintime]","loginlog","[uid]=" & [uid] & " and [logintime]>#" & [logintime] & "#")),Date(),DMin("[logintime]","loginlog","[uid]=" & [uid] & " and [logintime]>#" & [logintime] & "#"))))))<4) AND ((Max((DateDiff("d",IIf(IsNull(DMax("[logintime]","loginlog","[uid]=" & [uid] & " and [logintime]<#" & [logintime] & "#")),date()-day(date())+1,DMax("[logintime]","loginlog","[uid]=" & [uid] & " and [logintime]<#" & [logintime] & "#")),[logintime]))))<4));

还有，应加上限定日期的条件如：where date()-logintime<30 之类。

[懒得去死]

哦。谢谢LS的提示。我明白LZ的意思了。

[jack20021155]

加一个id字段，表示主键

select * from
(select distinct(u_id) from t_login) x
where
(select count(*) from t_login a
where a.u_id=x.u_id and
exists (select * from t_login b where b.u_id=x.u_id and b.id<>a.id and b.logintime in
(a.logintime,a.logintime+1,a.logintime+2,a.logintime+3)))+1 = (select count(*) from t_login d where d.u_id=x.u_id)

oracle可以，MySql不清楚

[洋溢2016]

o

[bzzun]

拿一号来举例，一号登陆的日期（去掉重复的）为 1，2，3，4，5，7，10，我的思路是拿1来举例查询出三天内是否登陆，如果三天内有，筛选出1，然后是2，查询出2三天内是否登陆，如果有，筛选出2，等等等等，最后是7，查询出7三天内是否登陆，如有
筛选出7，这样10就没筛选出来，所以一号总共连续登陆6次（一个月，去掉10，实际是7次）。拿5号来举例6，10 之间三天没登陆，6没有筛选出来，实际连续登陆是三次，最后和他实际登陆的次数（去掉重复的，一号实际是7次，连续登陆是6次加上最后一次）就能筛选出来
select log2.uid from (select distinct log1.uid,log1.logintime from loginlog as log1 where

log1.uid = (select distinct log4.uid from loginlog as log4 where log4.logintime=log1.logintime+1 and log1.uid =log4.uid)
or

log1.uid = (select distinct log5.uid from loginlog as log5 where log5.logintime=log1.logintime+2 and log1.uid =log5.uid)
or

log1.uid = (select distinct log6.uid from loginlog as log6 where log6.logintime=log1.logintime+3 and log1.uid =log6.uid ))as log2 group by log2.uid having

count(log2.uid)+1=(select count(log3.uid) from(select distinct(uid),logintime from loginlog)as log3 group by log3.uid having log3.uid = log2.uid)

[地下室小红叔]

嗯 关注

[playyuer]

select distinct uid 
from 
(
select distinct * 
from loginlog
) a
where 
exists
(select 1 
from loginlog 
where uid = a.uid
and
(
datediff(day,a.logintime,logintime)=1
and
datediff(month,a.logintime,logintime)=0
)
)
and
exists
(select 1 
from loginlog 
where uid = a.uid
and 
(
datediff(day,a.logintime,logintime)=2
and
datediff(month,a.logintime,logintime)=0
)

)
and
exists
(select 1 
from loginlog 
where uid = a.uid
and 
(
datediff(day,a.logintime,logintime)=3
and
datediff(month,a.logintime,logintime)=0
)
)

[懒得去死]

不知道LZ解决了没有

[懒得去死]

这个问题继续讨论

[libertine_dj]

13楼的思路，学习了。我按照自己更容易理解的思路，改写的sql：（比较纳闷可以用in（1，2，3），却不能用 <4)

select * from 
(
SELECT DISTINCT log1.uid,count(log1.uid) as aa
FROM loginlog AS log1
WHERE log1.uid = (
SELECT DISTINCT log4.uid
FROM loginlog AS log4
WHERE  DATEDIFF(log4.logintime,log1.logintime) in (1,2,3) 
AND log1.uid = log4.uid )
GROUP BY log1.uid
) a
left join 
(
SELECT DISTINCT uid,count(uid) as bb
FROM loginlog GROUP BY uid
) b on a.uid=b.uid where a.aa+1 = b.bb

[libertine_dj]

select * from 
(
SELECT DISTINCT log1.uid,count(log1.uid) as aa
FROM loginlog AS log1
WHERE log1.uid = (
SELECT DISTINCT log4.uid
FROM loginlog AS log4
WHERE  DATEDIFF( log4.logintime,log1.logintime) <= 3 && DATEDIFF( log4.logintime,log1.logintime) >=1
AND log1.uid = log4.uid )
GROUP BY log1.uid
) a
left join 
(
SELECT DISTINCT uid,count(uid) as bb
FROM loginlog GROUP BY uid
) b on a.uid=b.uid where a.aa+1 = b.bb;