22,209
社区成员
发帖
与我相关
我的任务
分享
create table tb(姓名 nvarchar(10),旷工日期 datetime)
insert into tb select '张三','2011-9-14'
insert into tb select '张三','2011-9-15'
insert into tb select '张三','2011-9-16'
insert into tb select '李四','2011-9-10'
insert into tb select '李四','2011-9-12'
insert into tb select '李四','2011-9-14'
insert into tb select '张三','2011-9-20'
go
;with cte as(
select *,1 as 连续旷工天数 from tb a where not exists(select 1 from tb where 姓名=a.姓名 and datediff(d,旷工日期,a.旷工日期)=1)
union all
select a.*,b.连续旷工天数+1 from tb a inner join cte b on a.姓名=b.姓名 and datediff(d,b.旷工日期,a.旷工日期)=1
)select * from cte order by 1,2
/*
姓名 旷工日期 连续旷工天数
---------- ----------------------- -----------
李四 2011-09-10 00:00:00.000 1
李四 2011-09-12 00:00:00.000 1
李四 2011-09-14 00:00:00.000 1
张三 2011-09-14 00:00:00.000 1
张三 2011-09-15 00:00:00.000 2
张三 2011-09-16 00:00:00.000 3
张三 2011-09-20 00:00:00.000 1
(7 行受影响)
*/
go
drop table tb
select name , not_worktime
from tablename t
where exists(select 1 from tablename where datediff(day,t.not_worktime,not_worktime)=1)
and exists(select 1 from tablename where datediff(day,t.not_worktime,not_worktime)=2)
create table tb(姓名 nvarchar(10),旷工日期 datetime)
insert into tb select '张三','2011-9-14'
insert into tb select '张三','2011-9-15'
insert into tb select '张三','2011-9-16'
insert into tb select '李四','2011-9-10'
insert into tb select '李四','2011-9-12'
insert into tb select '李四','2011-9-14'
insert into tb select '张三','2011-9-20'
go
select a.姓名,a.旷工日期,b.旷工日期,c.旷工日期
from tb a inner join tb b on a.姓名=b.姓名 and datediff(d,a.旷工日期,b.旷工日期)=1
inner join tb c on c.姓名=b.姓名 and datediff(d,b.旷工日期,c.旷工日期)=1
/*
姓名 旷工日期 旷工日期 旷工日期
---------- ----------------------- ----------------------- -----------------------
张三 2011-09-14 00:00:00.000 2011-09-15 00:00:00.000 2011-09-16 00:00:00.000
(1 行受影响)
*/
go
drop table tb