sql查询连续时间问题

Question

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 是连续的。麻烦各位了！

Answer 1

把having count(*)>=3改成having count(*)=3试试

Answer 2

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
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 是连续的。麻烦各位了！
*//*
u_id login_num
907 3
1100 3
*/

Answer 3

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 t as
(select row_number() over(partition by u_id order by logintime) rn,
logintime,u_id from loginlog
)
select a.u_id,
a.logintime 'logintime1',
b.logintime 'logintime2',
c.logintime 'logintime3'
from t a
left join t b on a.u_id=b.u_id and a.rn=b.rn-1
left join t c on a.u_id=c.u_id and a.rn=c.rn-2
where datediff(d,a.logintime,b.logintime)=1
and datediff(d,b.logintime,c.logintime)=1u_id        logintime1              logintime2              logintime3
----------- ----------------------- ----------------------- -----------------------
907         2011-12-16 00:00:00.000 2011-12-17 00:00:00.000 2011-12-18 00:00:00.000
1100        2011-12-14 00:00:00.000 2011-12-15 00:00:00.000 2011-12-16 00:00:00.000
1200        2011-12-16 00:00:00.000 2011-12-17 00:00:00.000 2011-12-18 00:00:00.000(3 row(s) affected)

Answer 4

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
*/

Answer 5

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)

Answer 6

请问有什么办法在原有的基础上过滤数据，比如说某个用户第一次10天连续登陆，然后第11天没登陆了，接下来他又连续登陆了15天，如果用连续登陆7天为条件查询的话，就会出现连续登陆10天的和15天的记录，有什么办法用sql查询时过滤掉那个10天连续登陆的数据，只显示该用户最多连续登陆的记录！刚解决一个问题上面又给了新问题。》.《

Answer 7

--呵呵昨天我写好了..你结贴了..今天直接复制
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-15',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
--同一个帐号,连续登陆的天数  
;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 * FROM cte where counts>1--鸭子,你又结贴了u_id        dn       counts
----------- -------- -----------
1200        20111212 1
1100        20111213 3
1200        20111213 4
907         20111215 3(4 行受影响)

Answer 8

再请教大家点问题哈，请问有什么办法在原有的基础上过滤数据，比如说某个用户第一次10天连续登陆，然后第11天没登陆了，接下来他又连续登陆了15天，如果用连续登陆7天为条件查询的话，就会出现连续登陆10天的和15天的记录，有什么办法用sql查询时过滤掉那个10天连续登陆的数据，只显示该用户最多连续登陆的记录！刚解决一个问题上面又给了新问题。            》.《

Answer 9

--哥.我又来了...
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-15',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
--同一个帐号,连续登陆的最大天数
;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) FROM cte GROUP BY cte.u_id

Answer 10

哥统计次数的列名怎么是叫无列名啊，不是应该叫 counts啊，怎么改都不行啊，求指点啊！

Answer 11

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-15',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
--同一个帐号,连续登陆的最大天数
;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

Answer 12

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-15',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
--同一个帐号,连续登陆的最大天数
;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