如果表里面有自动字段 id
Select id,updatetime
from tb1 a
where datediff(mi,时间,(select top 1 时间 from tb1 where id>a.id order by id))> 20
and a.状态='进门'
order by id
Select id,updatetime
from tb1 a
where datediff(mi,时间,(select top 1 时间 from tb1 where id>a.id order by id))> 20
and a.状态='进门'
order by id
DECLARE @t TABLE(姓名 VARCHAR(10), 时间 DATETIME,状态 VARCHAR(10),人员序号 INT,公司 VARCHAR(10))
INSERT @t SELECT '张三', '2008-1-1 8:10 ', '进门' , 1, '设计部'
UNION ALL SELECT '李四', '2008-1-1 8:20 ' , '出门' , 2 , '财务部'
UNION ALL SELECT '王五', '2008-1-1 8:30 ' , '进门' , 3 , '管理部'
UNION ALL SELECT '张三', '2008-1-1 8:40 ' , '出门' , 1 , '设计部'
UNION ALL SELECT '李四', '2008-1-1 8:30 ' , '进门' , 2 , '财务部'
UNION ALL SELECT '张三', '2008-1-1 9:20' , '进门' , 1 , '设计部'
UNION ALL SELECT '王五', '2008-1-1 11:50' ,'出门', 3 ,'管理部'
UNION ALL SELECT '王五', '2008-1-1 14:05' ,'进门', 3 ,'管理部'
SELECT * FROM @t a
WHERE 状态='出门' AND EXISTS
(SELECT 1 FROM @t b WHERE DATEDIFF(dd,时间,a.时间)=0 AND a.姓名=姓名 AND 状态='进门'
AND DATEDIFF(mi,a.时间,时间)>20
+
CASE WHEN DATEPART(hh,a.时间)<12 AND DATEPART(hh,时间)>=14 THEN 120
ELSE 0 END
AND NOT EXISTS(SELECT 1 FROM @t WHERE 姓名=b.姓名 AND 状态=b.状态
AND 时间>a.时间 AND 时间<b.时间)
)
Select id,姓名,时间
from tb1 a
where datediff(mi,时间,(select top 1 时间 from tb1 where id>a.id order by id))> 20
and a.状态='进门'
and ((datepart(h,a.时间)>8 and datepart(h,a.时间)<12)) or (datepart(h,a.时间)>8 and datepart(h,a.时间)<12))
order by id
insert into tb select '张三','2008-1-1 8:10:00','进门',1,'设计部'
union all select '李四','2008-1-1 8:20:00','出门',2,'财务部'
union all select '王五','2008-1-1 8:30:00','进门',3,'管理部'
union all select '张三','2008-1-1 8:40:00','出门',1,'设计部'
union all select '李四','2008-1-1 8:30:00','进门',2,'财务部'
union all select '张三','2008-1-1 9:20:00','进门',1,'设计部'
union all select '王五','2008-1-1 9:00:00','出门',3,'管理部'
go
select 姓名 from tb a where 状态='出门'
and exists(select 1 from tb b where 人员序号=a.人员序号 and 状态='进门' and 时间>a.时间 and datediff(mi,a.时间,时间)>20
and not exists(select 1 from tb where 人员序号=a.人员序号 and 状态='出门' and 时间>b.时间 and 时间<a.时间))go
drop table tb
/*
姓名
----------
张三(1 行受影响)
*/