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_idlogin_num
12003
*/
[解决办法]
create table loginlog(logintime datetime,u_id int)insert into loginlog select '2011-12-16',907insert into loginlog select '2011-12-17',907insert into loginlog select '2011-12-18',907insert into loginlog select '2011-12-14',1100insert into loginlog select '2011-12-15',1100insert into loginlog select '2011-12-16',1100insert into loginlog select '2011-12-13',1200insert into loginlog select '2011-12-14',1200insert into loginlog select '2011-12-16',1200insert into loginlog select '2011-12-17',1200insert 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 t2group by u_id,ddhaving count(*)>=3 --> 如果是连续10来天或很多天,修改此处的3.u_id login_num----------- -----------907 31100 31200 3(3 row(s) affected)
[解决办法]
create table #loginlog(logintime datetime,u_id int)insert into #loginlog select '2011-12-16',907insert into #loginlog select '2011-12-17',907insert into #loginlog select '2011-12-18',907insert into #loginlog select '2011-12-14',1100insert into #loginlog select '2011-12-15',1100insert into #loginlog select '2011-12-16',1100insert into #loginlog select '2011-12-13',1200insert into #loginlog select '2011-12-15',1200insert into #loginlog select '2011-12-16',1200insert into #loginlog select '2011-12-17',1200insert into #loginlog select '2011-12-18',1200go--同一个帐号,连续登陆的最大天数;WITH cte AS( SELECT b.u_id,CONVERT(CHAR(8),DATEADD(day,-sn,logintime),112)AS dn,COUNT(1) AS counts FROM ( SELECT u_id,ROW_NUMBER()OVER(PARTITION BY u_id ORDER BY logintime) AS sn,logintime FROM #loginlog )AS b GROUP BY b.u_id,CONVERT(CHAR(8),DATEADD(day,-sn,b.logintime),112))SELECT cte.u_id,MAX(counts) as counts FROM cte GROUP BY cte.u_id
