日期:2014-05-17  浏览次数:20591 次

编写sql语句,查询除了星期六星期日最长连续的数据

表的结构是这样的

------解决方案--------------------
又是孤岛问题
------解决方案--------------------
SQL code

select datepart(W,GETDATE())

/*  
name logindate  
a1 2011-1-2  
a1 2011-1-3  
a1 2011-1-4  
a1 2011-1-7  
a1 2011-1-12  
a1 2011-1-13  
a1 2011-1-16  
a2 2011-1-7  
a2 2011-1-8  
a2 2011-1-10  
a2 2011-1-11  
a2 2011-1-13  
a2 2011-1-24  
---------------------------------------------  
我需要的结果是:  
name start_day end_day logindays  
a1 2011-1-2 2011-1-4 3  
a2 2011-1-7 2011-1-8 2  
a2 2011-1-10 2011-1-11 2  
*/  
--> 测试数据:[tbl]  
if object_id('[tbl]') is not null drop table [tbl]  
create table [tbl]([name] varchar(2),[logindate] date)  
insert [tbl]  
select 'a1','2011-1-2' union all  
select 'a1','2011-1-3' union all  
select 'a1','2011-1-4' union all  
select 'a1','2011-1-7' union all  
select 'a1','2011-1-12' union all  
select 'a1','2011-1-13' union all  
select 'a1','2011-1-16' union all  
select 'a2','2011-1-7' union all  
select 'a2','2011-1-8' union all  
select 'a2','2011-1-10' union all  
select 'a2','2011-1-11' union all  
select 'a2','2011-1-13' union all  
select 'a2','2011-1-24'  

;with t as  
(  
select name,[logindate],dateadd(dd,  
-row_number()over(partition by name   
order by [logindate]),[logindate]) as diff from tbl  
),  
m as(  
select name,min([logindate]) as start_day,max([logindate]) as end_day,   
(datediff(dd,min([logindate]),max([logindate]))+1) as logindays  
from t  
group by name,diff  
)  
select name,start_day,end_day,logindays-
(select COUNT(1) from [tbl] c where c.name=a.name
 and c.logindate between a.start_day and a.end_day and DATEPART(W,c.logindate)=1 or 
 DATEPART(W,c.logindate)=7)+1 as logindays
from m a   
where logindays in(select MAX(logindays) from m b   
where a.name=b.name)  
/*  
name    start_day    end_day    logindays
a1    2011-01-02    2011-01-04    2
a2    2011-01-10    2011-01-11    2
a2    2011-01-07    2011-01-08    2
*/  


顺便改了一下