sql连续查天数问题

[zqz49877]

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
insert into #loginlog select '2011-12-19',1200
insert into #loginlog select '2011-12-20',1200
insert into #loginlog select '2011-12-21',1200
insert into #loginlog select '2011-12-21',1200
insert into #loginlog select '2011-12-22',1200
insert into #loginlog select '2011-12-23',1200
insert into #loginlog select '2011-12-24',1200
insert into #loginlog select '2011-12-25',1200
insert into #loginlog select '2011-12-26',1200
insert into #loginlog select '2011-12-27',1200
insert into #loginlog select '2011-12-28',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

这是查询账号连续几天登陆次数的
其中结果账号1200：显示9
事实上账号1200：应该显示是14
其中
insert into #loginlog select '2011-12-21',1200
insert into #loginlog select '2011-12-21',1200
有2条，是该用用户这天登陆了2次，如果'2011-12-21',1200数据只有一条的话，那结果就是正确的显示14
请问该怎么改才能支持有每天不断重复登陆的，麻烦大家了！！

[koumingjie]

将ROW_NUMBER改为 dense_rank

[koumingjie]

我数了一下1200 不算重复的， 应该是15次登录

[zqz49877]

[Quote=引用 2 楼 的回复:]

我数了一下1200 不算重复的， 应该是15次登录
[/Quote]
谢谢，不过我希望实现的是14，显示是的连续登陆的天数，因为1200只有14天连续登陆啊，麻烦了，麻烦了！

[开着拖拉机泡妞]

[Quote=引用楼主 的回复:]
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
inse……
[/Quote]

怎么又是这个问题？

[zqz49877]

[Quote=引用 4 楼 的回复:]

引用楼主 的回复:
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-……
[/Quote]
0.0用了段时间出问题了..

[-狙击手-]

;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,dense_rank()OVER(PARTITION BY u_id ORDER BY logintime) AS sn,logintime
  FROM #loginlog
  group by u_id,logintime 
  )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

[开着拖拉机泡妞]

create table #loginlog(logintime date,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
insert into #loginlog select '2011-12-19',1200
insert into #loginlog select '2011-12-20',1200
insert into #loginlog select '2011-12-21',1200
insert into #loginlog select '2011-12-21',1200
insert into #loginlog select '2011-12-22',1200
insert into #loginlog select '2011-12-23',1200
insert into #loginlog select '2011-12-24',1200
insert into #loginlog select '2011-12-25',1200
insert into #loginlog select '2011-12-26',1200
insert into #loginlog select '2011-12-27',1200
insert into #loginlog select '2011-12-28',1200

with t
as(
select ROW_NUMBER()over(PARTITION by u_id order by logintime) as rownum,
* from (select u_id,MAX(logintime) 
as logintime from #loginlog group by u_id,logintime)a
),
m as(
select u_id,logintime,DATEADD(DD,-rownum,logintime) as diff from t
)
select *,(DATEDIFF(DD,startdate,endtime)+1) as [days] from(
select u_id,MIN(logintime) as startdate,MAX(logintime) as endtime 
from m group by u_id,diff) a where DATEDIFF(DD,startdate,endtime)>=2
order by 1

/*
u_id	startdate	endtime	days
907	2011-12-16	2011-12-18	3
1100	2011-12-14	2011-12-16	3
1200	2011-12-15	2011-12-28	14
*/

[zqz49877]

[Quote=引用 7 楼 的回复:]

SQL code

create table #loginlog(logintime date,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
i……
[/Quote]
你的好细啊，谢谢，不过刚又发现了如果登陆时间2011-12-21 12:00：00，有具体时间加入的话，显示又错误了，有什么办法解决吗，或者先转成只显示年月日的再弄？麻烦了

[开着拖拉机泡妞]

with t
as(
select ROW_NUMBER()over(PARTITION by u_id order by convert(varchar(10),logintime,120)) 
as rownum,* from (select u_id,MAX(convert(varchar(10),logintime,120))
as logintime from #loginlog group by u_id,convert(varchar(10),logintime,120))a
),
m as(
select u_id,logintime,DATEADD(DD,-rownum,logintime) as diff from t
)
select *,(DATEDIFF(DD,startdate,endtime)+1) as [days] from(
select u_id,MIN(logintime) as startdate,MAX(logintime) as endtime 
from m group by u_id,diff) a where DATEDIFF(DD,startdate,endtime)>=2
order by 1
/*
u_id	startdate	endtime	days
907	2011-12-16	2011-12-18	3
1100	2011-12-14	2011-12-16	3
1200	2011-12-15	2011-12-28	14
*/

[zqz49877]

[Quote=引用 9 楼 的回复:]

SQL code

with t
as(
select ROW_NUMBER()over(PARTITION by u_id order by convert(varchar(10),logintime,120))
as rownum,* from (select u_id,MAX(convert(varchar(10),logintime,120))
as logintime from #……
[/Quote]
嗯，谢谢，都怪我数据给的不好，现在这语句会显示所有的连续登陆数据，比如一个11~13天，另一个15~28天的，都会显示出来，有没有办法只显示最新的登陆次数，就是15~28天，显示14的那个，这点分追问了这么都不好意思了，分只有那么多了0.0

[开着拖拉机泡妞]

[Quote=引用 10 楼 的回复:]

引用 9 楼 的回复:

SQL code

with t
as(
select ROW_NUMBER()over(PARTITION by u_id order by convert(varchar(10),logintime,120))
as rownum,* from (select u_id,MAX(convert(varchar(10),logintime,120))
……
[/Quote]
在查询出来的连续登陆的最后日期与当前日期做差，然后再选择差最小的那个就好了

[zqz49877]

[Quote=引用 11 楼 的回复:]

引用 10 楼 的回复:

引用 9 楼 的回复:

SQL code

with t
as(
select ROW_NUMBER()over(PARTITION by u_id order by convert(varchar(10),logintime,120))
as rownum,* from (select u_id,MAX(convert(varchar(10)……
[/Quote]
怎么实现呀，我数据库实在太糟糕了..