mysql 考勤表异常 【待修改】

有考勤刷卡记录表,表名为attendance ,有如下字段:
姓名 卡号 刷卡时间 刷卡类型 name id time type   
张三 59775623 2010-04-01 07:23:37 null   
张三 59775623 2010-04-01 07:50:21 null   
张三 59775623 2010-04-01 18:20:22 null   
张三 59775623 2010-04-01 18:50:53 null   
李四 59775624 2010-04-01 07:00:06 null   
李四 59775624 2010-04-01 18:00:12 null   
李四 59775624 2010-04-02 08:20:32 null
李四 59775624 2010-04-02 17:00:22 null
李四 59775624 2010-04-01 18:00:08 null
.....................................................................................................
以下还有很多,每位员工每天都有,...............
现在需要更新刷卡的数据,需要对表attendance执行一个update动作,根据刷卡时间,需满足如下功能
 
1.如果刷卡时间是8:00以前,则type的值update之后就为“上班”;
 
2.如果刷卡时间是17:30以后,则type的值update之后就为“下班”;
 
3.如果刷卡时间为8:00~~12:00之间,则type的值update之后就为“迟到”;
 
4.如果刷卡时间为13:00~~17:30之间,则type的值update之后就为“早退”;
 
5.如果同一个人同一天在12:00以前有多次刷卡,则刷卡时间最早的那一笔记录其type值为“上班”,其余12:00以前的刷卡记录其type值update之后,变为“上班重复刷卡;
 
6. 如果同一个人同一天在13:00以后有多次刷卡,则刷卡时间最迟的那一笔记录其type值为“下班”,其余13:00以后的刷卡记录其type值update之后,变为“下班重复刷卡;
 
7.其余每天的任何时间段,update后,type值变为“乱刷卡”
 
小弟最头痛的是其中的5、6两种情况,可以使用where group by + haviing count(*)>1将其查出来,update就不知道如何处理了,小弟思考了好几天,也只能做到这一步,实在做不下去了,跑来求助各位达人;
 
问题补充:
1.请各位达人务必注意那个时间的格式,SQL里面转换时间格式可以使用convert(char(10),time,120),输出为YYYYMMDD;convert(char(8),time,112),输出格式为YYYYMMDD;convert(char(10),time,108),输出为HH-MM-SS
 
use test
go
if object_id('test.dbo.attendance'is not null drop table attendance 
-- 创建数据表
create table attendance 
(
name char(5),
id int,
time datetime,
type char(20)
)
go
--插入测试数据
insert into attendance select '张三',59775623,'2010-04-01 07:23:37',null
union all select '张三',59775623,'2010-04-01 07:50:21',null
union all select '张三',59775623,'2010-04-01 18:20:22',null
union all select '张三',59775623,'2010-04-01 18:50:53',null
union all select '李四',59775624,'2010-04-01 07:00:06',null
union all select '李四',59775624,'2010-04-01 18:00:12',null
union all select '李四',59775624,'2010-04-02 08:20:32',null
union all select '李四',59775624,'2010-04-02 17:00:22',null
union all select '李四',59775624,'2010-04-02 18:18:08',null
union all select '王五',59775625,'2010-04-01 08:02:06',null
union all select '王五',59775625,'2010-04-01 18:00:12',null
union all select '王五',59775625,'2010-04-02 07:20:32',null
union all select '王五',59775625,'2010-04-02 12:35:22',null
union all select '王五',59775625,'2010-04-02 18:18:08',null
go
 
*/
 
-->更新数据
update attendance set type=t2.type
from attendance t1
inner join
(
    select name,id,time=_time,type=case when time<='08:00' and idd=1 then '上班'
        when time>'08:00' and time<='12:00' and idd=1 then '迟到'
        when time<'12:00' and idd<>1 then '上班重复刷卡'
        when time>='13:00' and time<='17:30' and idd=1 then '早退'
        when time>'17:30' and idd=1 then '下班'
        when time>'13:00' and idd<>1 then '下班重复刷卡' 
        when time>='12:00' and time<='13:00' then '乱刷卡' end
    from
    (
        select name,id,_time=time,time=convert(varchar(5),time,8),type,
        idd=row_number()over(partition by convert(varchar(10),time,120),name order by time)
        from attendance where convert(varchar(5),time,8)<='12:00'
            union all
        select name,id,_time=time,time=convert(varchar(5),time,8),type,
        idd=row_number()over(partition by convert(varchar(10),time,120),name order by time)
        from attendance where convert(varchar(5),time,8)>='13:00'
            union all
        select name,id,_time=time,time=convert(varchar(5),time,8),type,idd=0
        from attendance where convert(varchar(5),time,8)>='12:00'
        and convert(varchar(5),time,8)<='13:00'
    )t
) t2
on t1.id=t2.id and t1.time=t2.time
 
-->显示更新后数据
select from attendance
 
/*测试结果
 
name id time type
--------------------------------------------------------------
张三     59775623    2010-04-01 07:23:37.000    上班                
张三     59775623    2010-04-01 07:50:21.000    上班重复刷卡        
张三     59775623    2010-04-01 18:20:22.000    下班                
张三     59775623    2010-04-01 18:50:53.000    下班重复刷卡        
李四     59775624    2010-04-01 07:00:06.000    上班                
李四     59775624    2010-04-01 18:00:12.000    下班                
李四     59775624    2010-04-02 08:20:32.000    迟到                
李四     59775624    2010-04-02 17:00:22.000    早退                
李四     59775624    2010-04-02 18:18:08.000    下班重复刷卡        
王五     59775625    2010-04-01 08:02:06.000    迟到                
王五     59775625    2010-04-01 18:00:12.000    下班                
王五     59775625    2010-04-02 07:20:32.000    上班                
王五     59775625    2010-04-02 12:35:22.000    乱刷卡              
王五     59775625    2010-04-02 18:18:08.000    下班                
 
(14 行受影响)
*/
posted @ 2017-02-21 17:03  AlamZ  阅读(665)  评论(0编辑  收藏  举报