SQL 查询所有未打卡员工信息并创建视图

使用 SQL 查询所有未打卡员工信息并创建视图

本文将介绍如何使用 SQL 查询所有未打卡员工信息，并创建视图 table5 来存储查询结果。

示例数据库表：

员工信息表：employee

| 字段名称 | 数据类型 | 说明 | 示例数据 | |---|---|---|---| | emp_id | int 类型，索引 ID，可用于主键。 | 员工 ID | 1 | | emp_sex | char 类型，长度 40 | 员工性别 | 男 | | emp_email | varchar 类型，长度 50 | 员工邮箱 | 'liubei@shuguo.com' | | emp_salary | varchar 类型，长度 10 | 薪水 | 30000 | | emp_bonus | varchar 类型，长度 10 | 奖金 | 10000 | | emp_job_id | int 类型 | 工作 ID | 1 | | emp_dept_id | int 类型 | 部门 ID | 1 | | emp_manager | varchar 类型，长度 50 | 员工经理 | NULL | | emp_name | char 类型，长度 10 | 姓名 | '刘备' | | emp_date | date 类型 | 日期 | 2000-01-01 |

打卡记录表：attendance

| 字段名称 | 数据类型 | 说明 | 示例数据 | |---|---|---|---| | id | int 类型，索引 ID，可用于主键。 | 记录 ID | 1 | | check_date | date 类型 | 检查日期 | 2021-01-04 | | emp_id | int 类型 | 员工 ID | 1 | | clock_in | timestamp 类型 | 上班打卡 | 2021-01-04 08:34:02 | | clock_out | timestamp 类型 | 下班打卡 | 2021-01-04 18:33:12 |

日历信息表：calendar

| 字段名称 | 数据类型 | 说明 | 示例数据 | |---|---|---|---| | id | int 类型，索引 ID，可用于主键。 | 记录 ID | 1 | | calendar_date | date 类型 | 日期 | 2021/1/1 | | calendar_year | int 类型 | 年 | 2021 | | calendar_month | int 类型 | 月 | 1 | | calendar_day | int 类型 | 日 | 1 | | is_work_day | varchar 类型，长度 1 | 是否工作日 | 'N' |

SQL 查询语句：

SELECT c.calendar_date, e.emp_name, a.clock_in, a.clock_out
FROM calendar c
CROSS JOIN employee e
LEFT JOIN attendance a ON c.calendar_date = DATE(a.check_date) AND e.emp_id = a.emp_id
WHERE a.clock_in IS NULL OR a.clock_out IS NULL
AND c.is_work_day = 'Y'
ORDER BY c.calendar_date, e.emp_id

CREATE VIEW table5 AS
SELECT c.calendar_date, e.emp_name, a.clock_in, a.clock_out
FROM calendar c
CROSS JOIN employee e
LEFT JOIN attendance a ON c.calendar_date = DATE(a.check_date) AND e.emp_id = a.emp_id
WHERE a.clock_in IS NULL OR a.clock_out IS NULL
AND c.is_work_day = 'Y'
ORDER BY c.calendar_date, e.emp_id

代码解释：

使用 CROSS JOIN 将 calendar 表和 employee 表进行笛卡尔积，得到所有日期和所有员工的组合。
使用 LEFT JOIN 将 attendance 表与前面生成的组合进行左连接，根据日期和员工 ID 匹配打卡记录。
使用 WHERE 语句筛选出 clock_in 或 clock_out 为 NULL 的记录，即未打卡记录。
使用 AND c.is_work_day = 'Y' 过滤掉非工作日的记录。
使用 ORDER BY 语句按照日期和员工 ID 进行排序。
使用 CREATE VIEW table5 AS 语句创建名为 table5 的视图，存储查询结果。

注意：

上述代码假设 calendar 表中 is_work_day 字段的值为 'Y' 代表工作日，'N' 代表非工作日。
clock_in 和 clock_out 字段的数据类型为 timestamp，表示时间戳。
使用 DATE(a.check_date) 将 attendance 表中的 check_date 字段转换为日期类型，以匹配 calendar 表中的 calendar_date 字段。
CROSS JOIN 和 LEFT JOIN 的使用顺序可以根据实际情况进行调整。

总结：

通过以上 SQL 查询语句，可以查询所有未打卡员工信息，并创建名为 table5 的视图，存储查询结果。该视图可以用于后续的分析和处理。