SQL中ZK考勤机导入的CHECKINOUT表拆分签到签出及日期列方法

需求说明 #

已将ZK考勤机数据导入SQL数据库，CHECKINOUT表的考勤记录存储在checktime列中，需实现：

• 按用户+日期分组，每组第一条记录为签到时间，最后一条为签出时间
• 将checktime中的日期与时间拆分为独立列

现有数据信息 #

表结构 #

表名：CHECKINOUT
列：USERID（用户ID）、CHECKTIME（考勤时间，datetime类型）

当前查询语句 #

SELECT * FROM checkinout;

当前查询结果 #

USERID      CHECKTIME
----------- -----------------------
1           2022-10-04 10:25:44.000
1           2022-10-04 10:25:46.000
1           2022-10-04 10:30:55.000
1           2022-10-04 11:39:23.000
2           2022-10-07 14:05:22.000
2           2022-10-08 09:19:58.000
2           2022-10-08 15:34:53.000

期望结果 #

USERID      DATE                CHECKIN TIME       CHECK OUT TIME                                       
----------- ----------------------- ------------------ ------------------
1           2022-10-04          10:25:44.000       11:39:23.000
2           2022-10-07          14:05:22.000       14:05:22.000
2           2022-10-08          09:19:58.000       15:34:53.000

解决方案 #

适用于SQL Server的查询语句 #

SELECT
    USERID,
    CAST(CHECKTIME AS DATE) AS [DATE],
    CONVERT(VARCHAR(12), MIN(CHECKTIME), 114) AS [CHECKIN TIME],
    CONVERT(VARCHAR(12), MAX(CHECKTIME), 114) AS [CHECK OUT TIME]
FROM CHECKINOUT
GROUP BY USERID, CAST(CHECKTIME AS DATE)
ORDER BY USERID, [DATE];

逻辑说明

• CAST(CHECKTIME AS DATE)：提取CHECKTIME中的日期部分，作为分组维度之一
• MIN(CHECKTIME)：获取当日最早的考勤记录即签到时间，用CONVERT函数转为仅含时间的格式（样式114对应HH:MI:SS:mmm）
• MAX(CHECKTIME)：获取当日最晚的考勤记录即签出时间，同样转换为时间格式
• 按USERID和日期分组，确保每个用户每日仅生成一条汇总记录

适用于MySQL的查询语句 #

SELECT
    USERID,
    DATE(CHECKTIME) AS `DATE`,
    TIME(MIN(CHECKTIME)) AS `CHECKIN TIME`,
    TIME(MAX(CHECKTIME)) AS `CHECK OUT TIME`
FROM CHECKINOUT
GROUP BY USERID, DATE(CHECKTIME)
ORDER BY USERID, `DATE`;

逻辑说明

• DATE(CHECKTIME)：直接提取日期部分
• TIME()函数：从datetime类型值中提取时间部分，分别对当日最小、最大时间处理得到签到、签出时间
• 按用户ID和日期分组，实现每日汇总

内容的提问来源于stack exchange，提问作者Mohammad Imran