如何基于考勤数据表的CHECKTIME列按条件生成多列

原始考勤表（表名假设为attendance） #

ID | CHECKTIME
1  | 2023-08-01 07:22:30
1  | 2023-08-01 12:01:00
1  | 2023-08-01 12:43:00
1  | 2023-08-01 17:18:00
1  | 2023-08-02 05:13:30
1  | 2023-08-02 12:04:00
1  | 2023-08-02 12:33:00
1  | 2023-08-02 20:00:01
2  | 2023-08-01 06:11:30
2  | 2023-08-01 12:22:00
2  | 2023-08-01 12:53:00
2  | 2023-08-01 19:32:00
2  | 2023-08-02 07:11:30
2  | 2023-08-02 12:24:00
2  | 2023-08-02 12:37:00
2  | 2023-08-02 21:03:00

考勤时间规则 #

AM_IN  = CHECKTIME 时间区间：01:00 - 08:00
AM_OUT = CHECKTIME 时间区间：12:01 - 12:30
PM_IN  = CHECKTIME 时间区间：12:31 - 13:00
PM_OUT = CHECKTIME 时间区间：13:01 - 23:59

目标汇总表结构 #

ID | CHECKDATE  | AM_IN    | AM_OUT   | PM_IN    | PM_OUT   | 
1  | 2023-08-01 | 07:22:30 | 12:01:00 | 12:43:00 | 17:18:00 |
1  | 2023-08-02 | 05:13:30 | 12:04:00 | 12:33:00 | 20:00:01 |
2  | 2023-08-01 | 06:11:30 | 12:22:00 | 12:53:00 | 19:32:00 |
2  | 2023-08-02 | 07:11:30 | 12:24:00 | 12:37:00 | 21:03:00 |

不同数据库的实现代码 #

MySQL 版本 #

利用DATE()提取日期、TIME()提取时间，结合条件聚合函数按员工+日期分组：

SELECT 
    ID,
    DATE(CHECKTIME) AS CHECKDATE,
    MAX(CASE WHEN TIME(CHECKTIME) BETWEEN '01:00:00' AND '08:00:00' THEN TIME(CHECKTIME) END) AS AM_IN,
    MAX(CASE WHEN TIME(CHECKTIME) BETWEEN '12:01:00' AND '12:30:00' THEN TIME(CHECKTIME) END) AS AM_OUT,
    MAX(CASE WHEN TIME(CHECKTIME) BETWEEN '12:31:00' AND '13:00:00' THEN TIME(CHECKTIME) END) AS PM_IN,
    MAX(CASE WHEN TIME(CHECKTIME) BETWEEN '13:01:00' AND '23:59:59' THEN TIME(CHECKTIME) END) AS PM_OUT
FROM attendance
GROUP BY ID, DATE(CHECKTIME)
ORDER BY ID, CHECKDATE;

SQL Server 版本 #

使用CONVERT()函数处理日期和时间格式：

SELECT 
    ID,
    CONVERT(date, CHECKTIME) AS CHECKDATE,
    MAX(CASE WHEN CONVERT(time, CHECKTIME) BETWEEN '01:00:00' AND '08:00:00' THEN CONVERT(time, CHECKTIME) END) AS AM_IN,
    MAX(CASE WHEN CONVERT(time, CHECKTIME) BETWEEN '12:01:00' AND '12:30:00' THEN CONVERT(time, CHECKTIME) END) AS AM_OUT,
    MAX(CASE WHEN CONVERT(time, CHECKTIME) BETWEEN '12:31:00' AND '13:00:00' THEN CONVERT(time, CHECKTIME) END) AS PM_IN,
    MAX(CASE WHEN CONVERT(time, CHECKTIME) BETWEEN '13:01:00' AND '23:59:59' THEN CONVERT(time, CHECKTIME) END) AS PM_OUT
FROM attendance
GROUP BY ID, CONVERT(date, CHECKTIME)
ORDER BY ID, CHECKDATE;

注意事项 #

• 假设每个员工每天在每个时间区间只有1条打卡记录，若存在多条，MAX()会取该区间内最晚的打卡时间，MIN()则取最早的，可根据实际需求替换。
• 时间区间需精确到秒（如01:00:00），避免边缘时间匹配失效。

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