再次连续登陆天数有关问题

再次求助连续登陆天数问题？
create table loginlog(logintime datetime,u_id int)

insert into loginlog select '2011-04-01',907
insert into loginlog select '2011-04-02',907
insert into loginlog select '2011-04-03',907
insert into loginlog select '2011-04-07',1100
insert into loginlog select '2011-04-08',1100
insert into loginlog select '2011-04-09',1100
insert into loginlog select '2011-04-03',1200
insert into loginlog select '2011-04-04',1200
insert into loginlog select '2011-04-05',1200
insert into loginlog select '2011-04-07',1200
insert into loginlog select '2011-04-08',1200
insert into loginlog select '2011-04-09',1200


go

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

现在结果是
9072011-04-012011-04-033
11002011-04-072011-04-093
12002011-04-032011-04-053
12002011-04-072011-04-093

我想实现的是从今天往前计算连续登陆的数，那就应该过滤掉
9072011-04-012011-04-033
12002011-04-032011-04-053
只显示
11002011-04-072011-04-093
12002011-04-072011-04-093
这两条，请问各位怎么过滤啊？？


[解决办法]

SQL code

;with tas(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        ) aWHERE   DATEDIFF(DD, startdate, endtime) >= 2         AND endtime =CONVERT(VARCHAR(10),GETDATE(),120)  --这个地方限定一下即可。ORDER BY 1
[解决办法]
SQL code
declare @date datetime = '2011-04-10'; -- 11年的今天？with cte as(    select *, err=row_number()over(partition by u_id order by logintime desc)-datediff(day,logintime,@date) from loginlog)select u_id, min(logintime), max(logintime), count(1) from cte where err=0 group by u_id/*u_id                                                        ----------- ----------------------- ----------------------- -----------1100        2011-04-07 00:00:00.000 2011-04-09 00:00:00.000 31200        2011-04-07 00:00:00.000 2011-04-09 00:00:00.000 3(2 行受影响)*/