一、背景
师兄在面试时遇到了这条SQL题,回来我帮他参谋了下,觉得非常有意思,让我们一起来看看这道差点吊打师兄的笔试题吧!
对方给了两张表(分别是:派工记录表和打卡记录表),以及一张需求表(需要我们写查询语句得出),内容如下:
二、派工记录表
1、表示某人从某日开始到某日结束,按要求工作,派工期间每日打卡时间必须在“要求到岗时间”前(含要求时间,精确到分钟),否则迟到。
例如:
要求7:00,则6:59或者7:00:59 都不算迟到;7:01则视为迟到1分钟
2、行1中“派工结束日期”为null,表示此人的工作结束时间尚未确定,还在搬砖中;
行2中派工结束日期为2020-02-15,表示派工于02-15日结束。
3、假设员工名字不重复,每人只有一条派工信息
建表语句如下:
![d984ee42af7af54ca103174c6ce5a993.png](https://i-blog.csdnimg.cn/blog_migrate/f83b81c62fccd77e54f2b6176b8b033c.jpeg)
三、打卡记录表
在员工每次按指纹考勤时都会生成一条记录
建表语句如下:
![a4c4c77cc843632c7adc4e20f5ec8c50.png](https://i-blog.csdnimg.cn/blog_migrate/f0e35ced5a670b3000b3c52ea41ad3ec.jpeg)
四、需求
写一个查询语句,输入参数:日期(date),输出表格如下:
![64695323b0a622cd68b6ed56e1dee2dc.png](https://i-blog.csdnimg.cn/blog_migrate/817e4117116397a16f838409afd53ee0.jpeg)
注:
1、2020-02-16李四派工期已结束,不在派工期间不需要计算考勤,故不用显示
2、赵六在当日没有打卡,按照迟到算,迟到时间1440分钟
五、思路
我们要最终得到迟到时间,需要用表二中最早的打卡时间减去表一中要求的截至打卡时间,但很明显两个表日期的数据格式不一致,对表一的时间数据 sing_time 我们需要和年份进行拼接,然后再与表二的打卡时间进行比较,即可得出最终我们需要的迟到时间。
但在实际写查询语句时,我发现那样会过于繁琐,变考虑将上述步骤通过 SQL 中自定义函数来实现。先来复习下MySQL中的自定义函数。
自定义函数实例:
先来一个简单的,创建一个函数将'2009-06-23 00:00:00'这样格式的datetime时间转化为'2009年6月23日0时0分0秒'这样的格式:
![8b8de2ad731d1324e30e7faf86f5b126.png](https://i-blog.csdnimg.cn/blog_migrate/72dd073873b37a53785b15a81cebecd1.jpeg)
解析:
第一句是定义一个结束标识符,因为MySQL默认是以分号作为SQL语句的结束符的,而函数体内部要用到分号,所以会跟默认的SQL结束符发生冲突,所以需要先定义一个其他的符号作为SQL的结束符;
第二句是如果这个函数已经存在了,就删除掉,sp_test是数据库的名字,函数是跟数据库相关联的,getdate是函数的名字;
第三句是创建一个函数,()里是参数的名字和类型,RETURNS 定义这个函数返回值的类型;
函数体必须放在BEGIN END之间;
DECLARE 是定义函数体的变量,这里定义一个变量x,默认是空,然后SET给x变量赋值;
RETURN 是返回值,这里把变量x返回,x的类型必须与第三句中定义的返回类型一致。
调用:
![6c8a493a52cca4f11e5ece0d93dd8b92.png](https://i-blog.csdnimg.cn/blog_migrate/db788d7588c83d8874b566c3e6ffcd7a.jpeg)
六、最终答案
我们先来完成时间处理的自定义函数,代码如下:
![6280c98202fe3ac8f9f002956853f0fc.png](https://i-blog.csdnimg.cn/blog_migrate/1ea2a3b5834782de4ea3949f2cecc380.jpeg)
SQL 查询语句如下:
![05d03a5faadc431877272cdfc90e04ee.png](https://i-blog.csdnimg.cn/blog_migrate/bd9fef81ddf636c22ff350cda76777c9.jpeg)
最终结果还是非常完美的,如果小伙伴有更好的意见,欢迎留言讨论~