考情数据调整

维护维护账号(db_datareader)

补考情

识别号码 1000157

查询考勤

按时间段查询

select  t.sn 打卡机
  , t.*
from kaoqin..CHECKINOUT t
where USERID  = 2859
and  CHECKTIME between '2023-05-29' and '2023-05-30' 
order by checktime desc

按日期查询

 SELECT * 
 FROM  kaoqin..CHECKINOUT t
 WHERE USERID  = 2859 
 and convert(varchar(10),CHECKTIME,120)  = '2023-05-23'
 order by checktime desc

批量查询

select convert(varchar(10),CHECKTIME,120) 日期,COUNT(*) 次数 ,
明细  = STUFF( (
    select ',  ' + convert(varchar(10), CHECKTIME,108) 
    from   kaoqin..CHECKINOUT tv 
    WHERE USERID  = 2859  
    and convert(varchar(10),tv.CHECKTIME,120) =  convert(varchar(10),t.CHECKTIME,120) 
for xml path('')
)
                  ,1
                  ,1
                  ,'') 

from kaoqin..CHECKINOUT t
WHERE USERID  = 2859 
and CHECKTIME between '2023-05-01 ' and '2023-06-01' 
group by convert(varchar(10),CHECKTIME,120)
order by 次数  

STUFF 切割字符串

对应表格

插入对应sql

2023-05-23T12:25:23.000

insert into kaoqin..CHECKINOUT
(USERID, CHECKTIME, CHECKTYPE, VERIFYCODE, SENSORID, Memoinfo, WorkCode, sn, UserExtFmt, mask_flag, temperature)
values(2859, CAST(N'2023-05-23T12:25:23.000' AS DateTime),N'I', 1, 1, NULL, N'0', N'CQDT223160859', 1, 0, 0)

删除多余打卡

查看

 SELECT * 
 FROM  kaoqin..CHECKINOUT t
 WHERE USERID  = 2859 
 and convert(varchar(10),CHECKTIME,120)  = '2023-05-05'
 order by checktime desc
 
 
 
------------- 具体时间
select * from kaoqin..CHECKINOUT t
where  USERID  = 2859 
and CHECKTIME =  CAST(N'2023-05-23T12:25:23.000' AS DateTime)

清除数据

delete kaoqin..CHECKINOUT
where  USERID  = 2859 
and CHECKTIME =  CAST(N'2023-05-29 12:15:23.000' AS DateTime)

调整时间

 SELECT * 
 FROM  kaoqin..CHECKINOUT t
 WHERE USERID  = 2859 
 and convert(varchar(10),CHECKTIME,120)  = '2023-05-23'
 order by checktime desc

更新

update kaoqin..CHECKINOUT 
set  CHECKTIME =  CAST(N'2023-05-29 12:15:23.000' AS DateTime)
where  USERID  = 2859 
and CHECKTIME =  CAST(N'2023-05-29 12:15:23.000' AS DateTime)