sql查询连续时间问题
create table loginlog(logintime datetime,u_id int)

insert into loginlog select '2011-12-16',907
insert into loginlog select '2011-12-17',907
insert into loginlog select '2011-12-18',907
insert into loginlog select '2011-12-14',1100
insert into loginlog select '2011-12-15',1100
insert into loginlog select '2011-12-16',1100
insert into loginlog select '2011-12-13',1200
insert into loginlog select '2011-12-14',1200
insert into loginlog select '2011-12-16',1200
insert into loginlog select '2011-12-17',1200
insert into loginlog select '2011-12-18',1200
go
select distinct u_id,count(*) as login_num from loginlog
where logintime between convert(varchar(10),DATEADD(DD,-5,'2011-12-18'),120)  
and CONVERT(varchar(10),'2011-12-18',120)
group by u_id
having COUNT(*)>=3 order by login_num desc

目前这样的话只能查询到登陆的次数，如果“where logintime between convert(varchar(10),DATEADD(DD,-3,'2011-12-18')”DD后面的-3换成-5就会显示5条数据，我要的是显示连续登陆次数为3次，因为13~18只有16 17 18 是连续的。麻烦各位了！

------解决方案--------------------
go
if OBJECT_ID('loginlog') is not null
drop table loginlog
go
create table loginlog(
logintime datetime,
u_id int
)
go
insert into loginlog select '2011-12-16',907
insert into loginlog select '2011-12-17',907
insert into loginlog select '2011-12-18',907
insert into loginlog select '2011-12-14',1100
insert into loginlog select '2011-12-15',1100
insert into loginlog select '2011-12-16',1100
insert into loginlog select '2011-12-13',1200
insert into loginlog select '2011-12-14',1200
insert into loginlog select '2011-12-16',1200
insert into loginlog select '2011-12-17',1200
insert into loginlog select '2011-12-18',1200


;with T
as
(
select ROW_NUMBER()OVER(partition by u_id order by logintime asc)as num,
logintime as col1,u_id
from loginlog
)
select distinct b.u_id,count(*)as login_num from
(select T.col1,T.u_id,a.logintime as col2
from T left join 
(select ROW_NUMBER()OVER(partition by u_id order by logintime asc)as num,
* from loginlog)a
on T.u_id=a.u_id and T.num=a.num+1)b
where col1 between convert(varchar(10),DATEADD(DD,-5,'2011-12-18'),120)
and CONVERT(varchar(10),'2011-12-18',120)
and DATEDIFF(DD,col1,col2)=-1
group by u_id
having COUNT(*)>=3 order by login_num desc

/*
u_id login_num
1200 3
*/
------解决方案--------------------

SQL code

create table loginlog(logintime datetime,u_id int)

insert into loginlog select '2011-12-16',907
insert into loginlog select '2011-12-17',907
insert into loginlog select '2011-12-18',907
insert into loginlog select '2011-12-14',1100
insert into loginlog select '2011-12-15',1100
insert into loginlog select '2011-12-16',1100
insert into loginlog select '2011-12-13',1200
insert into loginlog select '2011-12-14',1200
insert into loginlog select '2011-12-16',1200
insert into loginlog select '2011-12-17',1200
insert into loginlog select '2011-12-18',1200


;with 
t1 as
(select row_number() over(partition by u_id order by logintime) rn,
logintime,u_id from loginlog
),
t2 as
(select rn,logintime,u_id,
rn-datediff(d,(select min(logintime) from t1),logintime) dd 
from t1
)
select u_id,count(*) 'login_num' 
from t2
group by u_id,dd
having count(*)>=3  --> 如果是连续10来天或很多天,修改此处的3.


u_id        login_num
----------- -----------
907         3
1100        3
1200        3

(3 row(s) affected)

------解决方案--------------------

SQL code

create table #loginlog(logintime datetime,u_id int)

insert into #loginlog select '2011-12-16',907
insert into #loginlog select '2011-12-17',907
insert into #loginlog select '2011-12-18',907
insert into #l