求SQL语句，查询连续3天有登陆的用户

Question

表结构如下logtable
username,action,time
我要查询连续3天内有action为"loging"的username
求助

Answer 1

select a.username
from logtable a,logtable b,logtable c
where a.time=b.time + 1 and b.time=c.time+1
and a.action='loging' and b.action='loging' and c.action='loging'
and a.usernaem=b.username and a.username=c.username

Answer 2

select * from logtable as a
where exists(select * from logtable
            where username = a.username
                and action='loging'
                and time > a.time and time < dateadd(day,3,a.time));

Answer 3

select username  from logtable where datediff( dd,time,getdate())>3

Answer 4

select username  from logtable 
where datediff( dd,getdate(),[time])>3 and [action]='loging'

Answer 5

Select t1.* 
from logtable t1 
join  logtable t2 on  t1.username=t2.username and dateadd(day,1,t1.[time])=t2.[time]
join  logtable t3 on  t2.username=t3.username and dateadd(day,1,t2.[time])=t3.[time]

Answer 6

create table logtable(username nvarchar(10),action nvarchar(10),[time] datetime)
goINSERT INTO LOGTABLE SELECT
'001','','2009-12-04' UNION ALL SELECT
'001','','2009-12-05'UNION ALL SELECT
'002','','2009-11-04'UNION ALL SELECT
'001','','2009-12-06'UNION ALL SELECT
'003','','2009-12-05'UNION ALL SELECT
'002','','2009-12-06' 
Select t1.username
from logtable t1 
join  logtable t2 on  t1.username=t2.username and dateadd(day,1,t1.[time])=t2.[time]
join  logtable t3 on  t2.username=t3.username and dateadd(day,1,t2.[time])=t3.[time]
/*
username
----------
001(1 行受影响)
*/
drop table logtable

Answer 7

create table logtable(username nvarchar(10),action nvarchar(10),[time] datetime)
goINSERT INTO LOGTABLE SELECT
'001','loging','2009-12-04' UNION ALL SELECT
'001','loging','2009-12-05'UNION ALL SELECT
'002','loging','2009-11-04'UNION ALL SELECT
'001','loging','2009-12-06'UNION ALL SELECT
'003','','2009-12-05'UNION ALL SELECT
'002','','2009-12-06' 
Select t1.username
from logtable t1 
join  logtable t2 on  T1.ACTION= 'loging' and  T2.ACTION= 'loging' 
 and t1.username=t2.username and dateadd(day,1,t1.[time])=t2.[time]
join  logtable t3 on  T3.ACTION= 'loging' and t2.username=t3.username and dateadd(day,1,t2.[time])=t3.[time] 
/*
username
----------
001(1 行受影响)
*/
drop table logtable

Answer 8

--下次条件想好了在提问
create table logtable(username nvarchar(10),action nvarchar(10),[time] datetime)
goINSERT INTO LOGTABLE SELECT
'001','loging','2009-12-04' UNION ALL SELECT
'001','loging','2009-12-05'UNION ALL SELECT
'002','loging','2009-11-04'UNION ALL SELECT
'001','loging','2009-12-06'UNION ALL SELECT
'003','','2009-12-05'UNION ALL SELECT
'002','','2009-12-06' 
Select t1.username
from logtable t1 
join  logtable t2 on  T1.ACTION= 'loging' and  T2.ACTION= 'loging' 
 and t1.username=t2.username and dateadd(day,1,t1.[time])=t2.[time]
join  logtable t3 on  T3.ACTION= 'loging' and t2.username=t3.username and dateadd(day,1,t2.[time])=t3.[time] 
where convert(nvarchar(10),t3.[time],120)=convert(nvarchar(10),getdate(),120)
 
drop table logtable

Answer 9

加条件
where 
  t1.[time]='你要的时间'

Answer 10

谢谢bancxc，不过你的还有个问题，重复记录的过滤最好是
Select DISTINCT  t1.username
from logtable t1 
join  logtable t2 on  T1.ACTION= 'loging' and  T2.ACTION= 'loging' 
                     and t1.username=t2.username and dateadd(day,1,t1.[time])=t2.[time]
join  logtable t3 on  T3.ACTION= 'loging' and t2.username=t3.username and dateadd(day,1,t2.[time])=t3.[time] 
where convert(nvarchar(10),t3.[time],120)=convert(nvarchar(10),getdate(),120)

Answer 11

create table logtable(username nvarchar(10),action nvarchar(10),[time] datetime)
goINSERT INTO LOGTABLE SELECT
'001','','2009-12-03' UNION ALL SELECT
'001','','2009-12-04'UNION ALL SELECT
'002','','2009-11-04'UNION ALL SELECT
'001','','2009-12-02'UNION ALL SELECT
'003','','2009-12-05'UNION ALL SELECT
'002','','2009-12-06'select a.username
from logtable a,logtable b ,logtable c
where datediff(day,a.time,b.time)=1 and 
  datediff(day,a.time,c.time)=2 and 
  datediff(day,c.time,getdate())=0username
----------
001(1 行受影响)drop table LOGTABLE 借8楼数据