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-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
请问该怎么改才能支持有每天不断重复登陆的，麻烦大家了！！

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

SQL code

;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

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

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