假设公司规定周一到周五（节假日除外）每天早上九点上班，下午六点下班，上下班都需要打卡。员工的打卡记录信息存储在attendance表中，同时日历信息存储calendar表中，这两个表的创建脚本如下：
-- 创建日历表calendarCREATE TABLE calendar(id INTEGER NOT NULL PRIMARY KEY, -- 日历编号calendar_date DATE NOT NULL UNIQUE, -- 日历日期calendar_year INTEGER NOT NULL, -- 日历年calendar_month INTEGER NOT NULL, -- 日历月calendar_day INTEGER NOT NULL, -- 日历日is_work_day VARCHAR(1) DEFAULT 'Y' NOT NULL -- 是否工作日);-- 创建考勤记录表attendance-- Oracle、MySQL、PostgreSQL 以及 SQLiteCREATE TABLE attendance(id INTEGER NOT NULL PRIMARY KEY, -- 考勤记录编号check_date DATE NOT NULL, -- 考勤日期emp_id INTEGER NOT NULL, -- 员工编号clock_in TIMESTAMP, -- 上班打卡时间clock_out TIMESTAMP, -- 下班打卡时间CONSTRAINT uk_attendance UNIQUE (check_date, emp_id));-- SQL ServerCREATE TABLE attendance(id INTEGER NOT NULL PRIMARY KEY, -- 考勤记录编号check_date DATE NOT NULL, -- 考勤日期emp_id INTEGER NOT NULL, -- 员工编号clock_in DATETIME2, -- 上班打卡时间clock_out DATETIME2, -- 下班打卡时间CONSTRAINT uk_attendance UNIQUE (check_date, emp_id));
以下是attendance表中的部分示例数据：
id|check_date|emp_id|clock_in |clock_out--|----------|------|-------------------|-------------------1|2021-01-04| 1|2021-01-04 08:34:02|2021-01-04 18:33:112|2021-01-04| 2|2021-01-04 08:10:31|2021-01-04 19:11:593|2021-01-04| 3|2021-01-04 08:54:08|2021-01-04 18:27:21...
其中，check_date是考勤日期，emp_id是员工编号，clock_in和clock_out分别是上班和下班打卡时间。
以下是calendar表中的部分示例数据：
id|calendar_date|calendar_year|calendar_month|calendar_day|is_work_day---|-------------|-------------|--------------|------------|-----------1| 2021-01-01| 2021| 1| 1|N2| 2021-01-02| 2021| 1| 2|N3| 2021-01-03| 2021| 1| 3|N...
其中canlendar_date是日历日期，canlendar_year、calendar_month、calendar_day分别是日历日期对应的年、月、日，is_work_day表示是否为工作日。
假如HR想要统计2021年1月份的员工缺勤记录，请问如何通过SQL查询相关记录？
首先，我们应该找出所有员工2021年1月份应该出勤的日期信息。这个数据可以通过日历表和员工表的交叉连接实现：
SELECT c.calendar_date, e.emp_nameFROM calendar cCROSS JOIN employee eWHERE c.calendar_year = 2021AND c.calendar_month = 1AND c.is_work_day = 'Y';
查询返回的结果如下。
calendar_date|emp_name-------------|--------2021-01-04|刘备2021-01-04|关羽2021-01-04|张飞...
如果某个员工在工作日没有打卡，attendance表中就会缺少相应的记录，表示缺勤。如果只有上班打卡，没有下班打卡，也当作缺勤处理。如果员工的上班打卡时间晚于9点，表示迟到。如果员工的下班打卡时间早于下午6点，表示早退。因此，我们可以将上面的查询结果和考勤表中的数据进行左外连接：
-- Oracle、MySQL以及PostgreSQLSELECT c.calendar_date, e.emp_name, a.clock_in, a.clock_outFROM calendar cCROSS JOINemployee eLEFT JOINattendance aON (a.check_date = c.calendar_date AND a.emp_id = e.emp_id)WHERE c.calendar_year = 2021 AND c.calendar_month = 1AND c.is_work_day = 'Y'AND (a.id IS NULLOR EXTRACT(hour FROMa.clock_in) >= 9OR EXTRACT(hour FROMa.clock_out) < 18OR a.clock_out IS NULL)ORDER BY c.calendar_date;
我们在左外连接中使用考勤日期和员工编号作为连接条件，同时在WHERE条件中增加了一个AND复合条件，返回了缺勤、迟到以及早退的数据。查询返回的结果如下。
calendar_date|emp_name|clock_in |clock_out-------------|--------|-------------------|-------------------2021-01-05|简雍 | |2021-01-11|关平 | |2021-01-13|周仓 |2021-01-13 08:34:28|2021-01-14|简雍 | |2021-01-19|孙乾 |2021-01-19 08:14:17|2021-01-19 17:44:08...
只要clock_out字段为空就是缺勤，clock_in时间晚于9点的数据就是迟到，clock_out时间早于18点的数据就是早退。
如果使用Microsoft SQL Server，我们需要将查询中的EXTRACT函数替换为DATEPART函数。如果使用SQLite，我们需要将查询中的EXTRACT函数替换为STRFTIME函数，再利用CAST函数将其转换为整数。这些函数的使用可以参考本书第3章。
我们还可以进一步利用搜索CASE表达式将查询结果进行转换显示：
-- Oracle、MySQL以及PostgreSQLSELECT c.calendar_date AS "考勤日期", e.emp_name AS "员工姓名",CASEWHEN a.clock_out ISNULL THEN '缺勤'WHEN EXTRACT(hourFROM a.clock_in) >= 9 THEN '迟到'ELSE '早退'END AS "考勤状态"FROM calendar cCROSS JOIN employee eLEFT JOIN attendance aON (a.check_date = c.calendar_date AND a.emp_id = e.emp_id)WHERE c.calendar_year = 2021 AND c.calendar_month = 1AND c.is_work_day = 'Y'AND (a.id IS NULLOR EXTRACT(hour FROM a.clock_in) >= 9OR EXTRACT(hour FROMa.clock_out) < 18OR a.clock_out IS NULL)ORDER BY c.calendar_date;
其中CASE表达式可以将打卡时间转换为考勤状态，查询返回的结果如下。
考勤日期 |员工姓名|考勤状态----------|-------|-------2021-01-05|简雍 |缺勤2021-01-11|关平 |缺勤2021-01-13|周仓 |缺勤2021-01-14|简雍 |缺勤2021-01-19|孙乾 |早退...
以上案例来自图书《SQL编程思想》。
原文作者：SQL编程思想；如有侵权，请联系管理员删除。
