关于2022“智警杯”一道SQL题目的思考和探讨

背景

事情是这样，在十月三十号的“智警杯”决赛里面，我们组只有一道题目没有解出来，就是一道我负责的SQL题目。比赛的时候为了这一道题写SQL写了接近一个小时，最终直到比赛结束也没有进展，也算是间接导致了刑警学院的保级队痛失冠军。后来发现我们学校其他组也全都没有解出这道题，于是对其一直念念不忘，趁这两天在宿舍全休的时间又尝试着解了一下，虽然做出了新的结果，可惜比赛已经结束，不知是否正确，故将其发布在CSDN上，欢迎知道答案或者想一起探讨这道题的大佬们来留言。

题目

因为比赛时间仓促，没有将题目和题目要求完整复制下来，从同学手里要了份数据。是一个关于警员在2021年1月份上下班打卡信息的一个题目。

calender表：

顾名思义，日历表，分别有如下字段，id：日历表的编号，题目中基本上用不到；calendar_date：日期信息；calender_year、calender_month、calender_day：分别是日期信息的年份、月份、日期；is_work_day：是否是工作日，用Y和N标识（Y表示工作日，意味着需要打卡；N表示非工作日，意味着不需要打卡）。部分数据如下图。

attendance表：

 记录警员打卡信息的表。分别有如下字段：id：打卡信息的编号；check_date：打卡日期；emp_id：警员的id信息；clock_in和clock_out：记录某警员的签到打卡时间、签退打卡时间。部分数据如下图。

employee表：

警员信息表。该表中对解题有用的字段只有emp_id 即警员id以及emp_name 即警员姓名，其他字段在解答该题的时候完全没用到。 部分数据如下图。

 赛场上没有验证过的题目就是该大题的最后一个小题：

10. 查询所有未打卡的人员信息（即clock_in或者clock_out为MULL），结果存入视图table5。(字段：calendar_date、emp_name、clock_in、clock_out)

思路和讨论

在十月三十号打比赛的时候，打眼一看，认为此题非常简单，于是飞快写出代码

SELECT check_date as calendar_date , emp_id , clock_in , clock_out
FROM attendance
WHERE clock_in IS NULL OR clock_out IS NULL

对应查询出1条记录：

 但是却怎么验证也验证不过，甚至我返回表去一条一条记录去看clock_in和clock_out字段哪里是空的（所幸表数据并不多），但是却始终无法找到验证不通过的原因。后来通过尝试发现了一些端倪。

首先从calender表中，通过最简单的聚合函数count，不难查出有20个需要打卡的工作日。

SELECT COUNT(*)
FROM calendar
WHERE is_work_day = "Y";

 再从attendance表中，查看是否所有人的打卡记录都是20天：

SELECT emp_id , count(emp_id) as clock_count
FROM attendance
GROUP BY emp_id
HAVING count(emp_id) <> (SELECT count(1) FROM calendar WHERE is_work_day = "Y");

至此发现端倪：在2021年1月应有20个工作日，但是却查询到部分警员打卡记录不够20条，于是猜测是不是没有打卡信息就不会出现在attendance表中？从而接下来的工作就比较简单：查询出来这些没有在attendance表中出现的警员的缺勤记录，并且和比赛时查出的那一条记录做一个union操作，从而查到所有的未打卡人员信息。这个工作就非常简单了，无非就是几个子查询，差集，表的连接查询这些基础知识的运用，十几行代码搞定。

SELECT t.calendar_date , e.emp_name , t.clock_in , t.clock_out
FROM (
SELECT t1.* , NULL as clock_in , NULL as clock_out
FROM (
SELECT c.calendar_date , e.emp_id 
FROM calendar c
JOIN  employee e ON c.is_work_day = "Y") t1
LEFT JOIN (
SELECT check_date , emp_id
FROM attendance) t2
ON t1.calendar_date = t2.check_date AND t1.emp_id = t2.emp_id
WHERE t2.emp_id is NULL
UNION
SELECT check_date as calendar_date , emp_id , clock_in , clock_out
FROM attendance
WHERE clock_in IS NULL OR clock_out IS NULL) t
INNER JOIN employee e on t.emp_id = e.emp_id

于是就查询出了如下结果：

 总结和思考

上面的分析和查询都是赛后复盘做的工作，可惜主办方红亚科技并未放出参考答案，我也无从得知我的答案是否能验证的过，不过这道题目本身还是挺有意思，也给我带来了很多启发和思考。也欢迎一起打今年智警杯决赛的警校兄弟们一起讨论，以及当时验证通过的大佬们能否看一看本菜鸡的思路和答案是否正确，本人不胜感激。