如何检索一个月内连续登入的用户? 要求:
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
解决方案 »
- mysql varchar最大长度问题?
- mysql设置事务隔离级别没有效果???
- c/c++中如何 取得执行存储过程的多结果集...
- 将A表某个字段拷贝到B表的一个字段怎么写?
- 调用存储过程错误
- redhat linux7.3下安装mysql-5.0.22时错误
- 请问如何返回有限行数的结果,类似oracle的where rownum < n
- 哪有PostGreSql for window的下载?
- mysql 存储过程中使用临时表出现doesn exist 错误
- excel导入数据到mysql
- 终于编译通过Win32版的MySQL了(支持中文全文索引的),散分……
- 把光盘中的数据库复制到MySql的按装目录/data之下,不好用
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;
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;
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",楼主应按实际需要设置此值,另外,如果是最后三天没登录,只是这样也是不能反映出来的,因为不知楼主是否说这是在今日之前的一个月,这里也不妄作判断了。
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 之类。
(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不清楚
筛选出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)
orlog1.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 havingcount(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)
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
)
)
(
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
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;