sql自定义函数_师兄大厂面试遇到这条 SQL 数据分析题，差点含泪而归！

一、背景

师兄在面试时遇到了这条SQL题，回来我帮他参谋了下，觉得非常有意思，让我们一起来看看这道差点吊打师兄的笔试题吧！

对方给了两张表(分别是：派工记录表和打卡记录表)，以及一张需求表(需要我们写查询语句得出)，内容如下：

二、派工记录表

1、表示某人从某日开始到某日结束，按要求工作，派工期间每日打卡时间必须在“要求到岗时间”前(含要求时间，精确到分钟)，否则迟到。

例如：

要求7：00，则6:59或者7:00:59 都不算迟到；7:01则视为迟到1分钟

2、行1中“派工结束日期”为null，表示此人的工作结束时间尚未确定，还在搬砖中；

行2中派工结束日期为2020-02-15，表示派工于02-15日结束。

3、假设员工名字不重复，每人只有一条派工信息

建表语句如下：

三、打卡记录表

在员工每次按指纹考勤时都会生成一条记录

建表语句如下：

四、需求

写一个查询语句，输入参数：日期(date)，输出表格如下：

注：

1、2020-02-16李四派工期已结束，不在派工期间不需要计算考勤，故不用显示

2、赵六在当日没有打卡，按照迟到算，迟到时间1440分钟

五、思路

我们要最终得到迟到时间，需要用表二中最早的打卡时间减去表一中要求的截至打卡时间，但很明显两个表日期的数据格式不一致，对表一的时间数据 sing_time 我们需要和年份进行拼接，然后再与表二的打卡时间进行比较，即可得出最终我们需要的迟到时间。

但在实际写查询语句时，我发现那样会过于繁琐，变考虑将上述步骤通过 SQL 中自定义函数来实现。先来复习下MySQL中的自定义函数。

自定义函数实例：

先来一个简单的，创建一个函数将'2009-06-23 00:00:00'这样格式的datetime时间转化为'2009年6月23日0时0分0秒'这样的格式：

解析：

第一句是定义一个结束标识符，因为MySQL默认是以分号作为SQL语句的结束符的，而函数体内部要用到分号，所以会跟默认的SQL结束符发生冲突，所以需要先定义一个其他的符号作为SQL的结束符；

第二句是如果这个函数已经存在了，就删除掉，sp_test是数据库的名字，函数是跟数据库相关联的，getdate是函数的名字；

第三句是创建一个函数，()里是参数的名字和类型，RETURNS 定义这个函数返回值的类型；

函数体必须放在BEGIN END之间；

DECLARE 是定义函数体的变量，这里定义一个变量x,默认是空，然后SET给x变量赋值；

RETURN 是返回值，这里把变量x返回，x的类型必须与第三句中定义的返回类型一致。

调用：

六、最终答案

我们先来完成时间处理的自定义函数，代码如下：

SQL 查询语句如下：

最终结果还是非常完美的，如果小伙伴有更好的意见，欢迎留言讨论~