EnNo DT
6 2013-7-2 14:05
1 2013-7-2 14:11
2 2013-7-2 14:12
2 2013-7-2 14:12
6 2013-7-2 14:14
6 2013-7-2 14:14
1 2013-7-2 14:15
3 2013-7-2 14:16
3 2013-7-2 14:16
4 2013-7-2 14:29
4 2013-7-2 14:29
表如上
每个EnNo一个月内每天分别在
8：10 之前 有一次记录
11:30-1:30之间有两次记录
5：30之后又一次记录
要求查询出来不符合上述规律的当天的打卡记录
------解决方案--------------------
select * from table a where not exists
(
select EnNo from AuctionSell where DT ('一堆时间上的条件') and EnNo=a.EnNo
group by EnNo
having count(1)>=4
)
------解决方案--------------------
IF OBJECT_ID('TEMPDB.DBO.#T1') IS NOT NULL DROP TABLE #T1
GO
CREATE TABLE #T1
(
EnNo int,
DT DATETIME
)
Insert into #T1
VALUES
(6,'2013-7-2 7:05'),
(6,'2013-7-2 12:05'),
(6,'2013-7-2 12:31'),
(6,'2013-7-2 14:05'),
(1,'2013-7-2 14:11'),
(2,'2013-7-2 14:12'),
(2,'2013-7-2 14:12'),
(6,'2013-7-2 14:14'),
(6,'2013-7-2 14:14'),
(1,'2013-7-2 14:15'),
(3,'2013-7-2 14:16'),
(3,'2013-7-2 14:16'),
(4,'2013-7-2 14:29'),
(4,'2013-7-2 14:29'),
(6,'2013-7-2 17:33');
with t as (select *,CONVERT(varchar(19),DT,120) s from #T1)
select * from t where
not(exists (select 1 from t t1 where LEFT(t1.s,10)=LEFT(t.s,10) and RIGHT(t1.s,8)<'08:10:00' and t1.EnNo=t.EnNo group by t1.EnNo having count(1)>=1)
and exists (select 1 from t t1 where LEFT(t1.s,10)=LEFT(t.s,10) and RIGHT(t1.s,8) between '11:30:00' and '13:29:59' and t1.EnNo=t.EnNo group by t1.EnNo having count(1)>=2)
and exists (select 1 from t t1 where LEFT(t1.s,10)=LEFT(t.s,10) and RIGHT(t1.s,8)>='17:30:00' and t1.EnNo=t.EnNo group by t1.EnNo having count(1)>=1)
)order by EnNo
------解决方案--------------------
select *
from [表名]
where not (right(DT,5)<'08:10' or
right(DT,5) between '11:30' and '13:30' or
right(DT,5)>'17:30')
------解决方案--------------------
直接写SQL代码的，一定没玩过考勤
这是设计问题，非SQL语句问题