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

2007-11-08 03:37:05 606 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

程式人生2000 墨衍会员 · AI 创作全网分发关注 + 加入社区

Spring MVC——登入功能的实现 Spring MVC——登入功能的实现基本思路：利用前端的post方式传送数据给后台，后台根据收到的数据，调用数据库，根据用户名进行数据查询，接收查询结果以后，与前端传送来的数据进行匹配，若用户名不存在，则弹出“不存在该用户”，若密码错误则弹出“密码错误”，若用户名密码都与数据库信息匹配，则登入成功。代码：在dao层添加函数，根据用户名查询数据库中对应用户的信息。 User getUser... 阅读详情

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

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

...全文

实时检索匹配、用户问题学习、知识库动态更新短期靠检索算法提升匹配精度；长期靠 “用户反馈 - 人工迭代” 闭环扩大知识库覆盖；大模型则在中间层起到 “临时兜底” 和 “辅助挖掘” 的作用。更新频率无固定标准，核心是让知识库的迭代速度跟上业务和用户需求的变化速度。阅读详情

程式人生2000

MySQL 57,062 成员 56,759 内容

+ 关注 + 加入社区

切换为时间正序

libertine_dj 2009-02-23

打赏
举报



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;

libertine_dj 2009-02-23

打赏
举报

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

懒得去死 2007-12-18

打赏
举报

这个问题继续讨论

懒得去死 2007-12-01

打赏
举报

不知道LZ解决了没有

playyuer 2007-11-11

打赏
举报



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

)

)

地下室小红叔 2007-11-10

打赏
举报

嗯关注

bzzun 2007-11-10

打赏
举报

拿一号来举例，一号登陆的日期（去掉重复的）为 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)

洋溢2020 2007-11-10

打赏
举报

jack20021155 2007-11-10

打赏
举报

加一个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不清楚

懒得去死 2007-11-09

打赏
举报

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

lijiaxiang 2007-11-09

打赏
举报

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 之类。

lijiaxiang 2007-11-09

打赏
举报

楼主说的是连续三天，如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",楼主应按实际需要设置此值，另外，如果是最后三天没登录，只是这样也是不能反映出来的，因为不知楼主是否说这是在今日之前的一个月，这里也不妄作判断了。

懒得去死 2007-11-09

打赏
举报

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

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

yxh007 2007-11-08

打赏
举报

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

yxh007 2007-11-08

打赏
举报

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

懒得去死 2007-11-08

打赏
举报



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;

懒得去死 2007-11-08

打赏
举报

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

懒得去死 2007-11-08

打赏
举报



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;

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

相关推荐