22,301
社区成员




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
CREATE TABLE [dbo].[OA](
[F_NAME] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[F_DATE] [datetime] NULL
)
insert into oa values('张三','20110914')
insert into oa values('张三','20110915')
insert into oa values('张三','20110916')
insert into oa values('李四','20110910')
insert into oa values('李四','20110912')
insert into oa values('李四','20110914')
insert into oa values('张三','20110920')
SELECT A.* FROM OA A
INNER JOIN OA B
ON A.F_NAME = B.F_NAME
AND A.F_DATE = B.F_DATE - 1
INNER JOIN OA C
ON B.F_NAME = C.F_NAME
AND B.F_DATE = C.F_DATE - 1