sql连续查天数有关问题

2012-04-10

sql连续查天数问题create table #loginlog(logintime datetime,u_id int)insert into #loginlog select 2

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 codewith 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) a where DATEDIFF(DD,startdate,endtime)>=2order by 1/*u_id    startdate    endtime    days907    2011-12-16    2011-12-18    31100    2011-12-14    2011-12-16    31200    2011-12-15    2011-12-28    14*/

热点排行

Bad Request.

SQL Server

sql连续查天数有关问题