我哭 好吧 我还是手打吧。。 log_id bigint not null primary key(log_id) identity(1,1), --日志主键ID title nvarchar(20) not null, --日志标题 create_day varchar(10) not null, --创建日期,格式:yyyy-MM-dd create_time varchar(10), --创建时间,格式:hh:mm:ss content nvarchar(500), --内容 updateTime varchar(10), --日志更新时间 log_state varchar(100), --日志状态, user_ids bigint, --外键ID,主键USER表主键 note varchar(20) --备注
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 */ 顺便改了一下
log_id bigint not null primary key(log_id) identity(1,1), --日志主键ID
title nvarchar(20) not null, --日志标题
create_day varchar(10) not null, --创建日期,格式:yyyy-MM-dd
create_time varchar(10), --创建时间,格式:hh:mm:ss
content nvarchar(500), --内容
updateTime varchar(10), --日志更新时间
log_state varchar(100), --日志状态,
user_ids bigint, --外键ID,主键USER表主键
note varchar(20) --备注
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
*/
顺便改了一下