摘要：
将每个人当天的多个出勤记录汇总为一个当天的记录。摘要格式如下：07:40；07:49; 07:51; 11:13; 16:08; 17:14。 考勤汇总每月生成一个表。ghbadgunmbernamedaysweekchecktime424-20100817772021-03-19108:12；17:24; 16:43; 08:30干货如下。所使用的知识点是触发点。触发器使用插入批处理插入，触发器的临时表将有多条记录。所以我们也使用游标的知识。
把每个人当天的多条考勤记录，汇总成 当天的一条记录。汇总格式如：07:40;07:49;07:51;11:13;16:08;17:14。
考勤汇总按每个月生成一张表。kq2103list。格式如下。
gh badgenumber name days week checktimes
424-201008 1777 2021-03-19 1 08:12;17:24;16:43;08:30
干货如下，用的知识点是触发器，触发器用到 insert 批量插入，触发器的临时表就会有多条记录。所以还用到游标的知识。
alter Trigger tri_Check_insert On kqjl For Insert AS --定义变量 declare @CurMonth varchar(4), @name varchar(20) declare @gh VARCHAR(10),@kq_bh VARCHAR(10),@kq_name VARCHAR(20), @checktype VARCHAR(1), @verifycode VARCHAR(8), @sensorid VARCHAR(8),@flag VARCHAR(1),@id int DECLARE @checktime datetime declare @sql NVARCHAR(2000) declare @sTable varchar(10) --在inserted表中查询已经插入记录信息 select @CurMonth =CurMonth,@kq_bh=badgenumber,@checktime=checktime,@checktype=checktype,@verifycode=verifycode,@sensorid=sensorid,@flag=flag,@id=id from inserted; declare cur_rankschange Cursor For select CurMonth,badgenumber,checktime,checktype,verifycode,sensorid,flag,id from inserted; -- 这个是表 --INSERT INTO dbo.kqjl_temp2([CurMonth],[gh],[badgenumber],[checktime],[machine_sn]) values(@CurMonth,@gh,@kq_bh,@checktime,@id) Open cur_rankschange fetch next from cur_rankschange into @CurMonth,@kq_bh,@checktime,@checktype,@verifycode,@sensorid,@flag,@id while @@fetch_status=0 begin select @gh=gh,@name=name from HR.dbo.kqrs with (nolock) where badgenumber=@kq_bh if not exists (select 1 from kqjl with (nolock) where CurMonth=@CurMonth and gh=@kq_bh and id<@id and abs(datediff(ss,checktime,@checktime))<60) begin set @sTable='kq'+@CurMonth+'list' set @sql='if exists(select 1 from '+@sTable+' with (nolock) where gh='''+@gh+''' and [days]='''+CONVERT(varchar(10),@checktime,120)+''') ' set @sql=@sql+'update '+@sTable+' WITH (ROWLOCK) set checktimes=checktimes+'';''+'''+CONVERT(varchar(5), @checktime, 114 )+''',[week]=1 where [days]='''+CONVERT(varchar(10),@checktime,120)+''' and gh='''+@gh+''' ' set @sql=@sql+'else ' set @sql=@sql+'insert into '+@sTable+'(gh,badgenumber,name,[days],checktimes,[week]) values('''+@gh+''','+@kq_bh+','''+@name+''','''+CONVERT(varchar(10),@checktime,120)+''','''+CONVERT(varchar(5),@checktime,114)+''',1)' exec(@sql) end --else --begin -- set @sTable='kq'+@CurMonth+'list' -- set @sql='if not exists(select 1 from '+@sTable+' with (nolock) where gh='''+@gh+''' and [days]='''+CONVERT(varchar(10),@checktime,120)+''') ' -- set @sql=@sql+'insert into '+@sTable+'(gh,badgenumber,name,[days],checktimes,[week]) values('''+@gh+''','+@kq_bh+','''+@name+''','''+CONVERT(varchar(10),@checktime,120)+''','''+CONVERT(varchar(5),@checktime,114)+''',1)' -- exec(@sql) --end fetch next from cur_rankschange into @CurMonth,@kq_bh,@checktime,@checktype,@verifycode,@sensorid,@flag,@id end close cur_rankschange deallocate cur_rankschange go