sql如何删除每个员工同一天除了最早和最晚的其他纪录.
来源:百度知道 编辑:UC知道 时间:2024/06/25 08:47:55
还需要判断是同一个员工,同一天的纪录,不能和以前的纪录比较,得到的结果是每个员工最早和最晚的2条纪录.希望能够得到高手的完善.谢谢!!
楼上的两位请注意这个表中是有多天的记录, 并不只是一天的.
谨慎操作, 先把delete from timerecords改成select查一下是否是想要删除的结果, 然后再delete.
delete from timerecords
where exists (select 'x' from (
select card_id,max(sign_time) sign_time, convert(varchar(10),sign_time,120) current_day from timerecords
group by card_id,convert(varchar(10),sign_time,120)) a where timerecords.card_id = a.card_id and timerecords.sign_time != a.sign_time and convert(varchar(10),timerecords.sign_time,120) = current_day)
and exists (select 'x' from (
select card_id,min(sign_time) sign_time, convert(varchar(10),sign_time,120) current_day from timerecords
group by card_id,convert(varchar(10),sign_time,120)) a where timerecords.card_id = a.card_id and timerecords.sign_time != a.sign_time and convert(varchar(10),timerecords.sign_time,120) = current_day)
给个删除的,oracle的,其他同理
DELETE TIMERECORDS
WHERE CARD_ID || TO_CHAR(SIGN_TIME, 'yyyy-mm-dd hh:mi:ss') <