? 作者?|??云祁???

來源 |?cnblogs.com/beiisbei/p/13269964.html

一、背景

師兄在面試時(shí)遇到了這條SQL題，回來我?guī)退麉⒅\了下，覺得非常有意思，讓我們一起來看看這道差點(diǎn)吊打師兄的筆試題吧！?

對方給了兩張表(分別是：派工記錄表和打卡記錄表)，以及一張需求表(需要我們寫查詢語句得出)，內(nèi)容如下：

二、派工記錄表

1、表示某人從某日開始到某日結(jié)束，按要求工作，派工期間每日打卡時(shí)間必須在“要求到崗時(shí)間”前(含要求時(shí)間，精確到分鐘)，否則遲到。

例如：

要求7：00，則6:59或者7:00:59 都不算遲到；7:01則視為遲到1分鐘

2、行1中“派工結(jié)束日期”為null，表示此人的工作結(jié)束時(shí)間尚未確定，還在搬磚中；
行2中派工結(jié)束日期為2020-02-15，表示派工于02-15日結(jié)束。

3、假設(shè)員工名字不重復(fù)，每人只有一條派工信息

建表語句如下：

create?table?work_plan
(
worker_name varchar(10), --人員
start_date date, --派工起始日
end_date date, --派工截止日
sign_time varchar(10) --派工期間的需要打卡時(shí)間
)

insert?into?work_plan values
('張三','2020-01-01',null,'06:30'),
('李四','2020-02-01','2020-02-15','07:00'),
('王五','2019-12-29','2020-03-30','06:00'),
('趙六','2019-12-29','2020-03-30','06:00')

三、打卡記錄表

在員工每次按指紋考勤時(shí)
都會生成一條記錄

建表語句如下：

create?table?sign_log
(
worker_name varchar(10),
sign_time datetime
)

insert?into?sign_log?values??
('張三','2020-02-16 04:01'),
('張三','2020-02-16 05:02'),
('張三','2020-02-16 06:03'),
('王五','2020-02-16 07:03'),
('王五','2020-02-16 08:03'),
('王五','2020-02-16 09:03')

四、需求

寫一個(gè)查詢語句，輸入?yún)?shù)：日期(date)，輸出表格如下：

注：
1、2020-02-16李四派工期已結(jié)束，不在派工期間不需要計(jì)算考勤，故不用顯示
2、趙六在當(dāng)日沒有打卡，按照遲到算，遲到時(shí)間1440分鐘

五、思路

我們要最終得到遲到時(shí)間，需要用表二中最早的打卡時(shí)間減去表一中要求的截至打卡時(shí)間，但很明顯兩個(gè)表日期的數(shù)據(jù)格式不一致，對表一的時(shí)間數(shù)據(jù) sing_time 我們需要和年份進(jìn)行拼接，然后再與表二的打卡時(shí)間進(jìn)行比較，即可得出最終我們需要的遲到時(shí)間。?

但在實(shí)際寫查詢語句時(shí)，我發(fā)現(xiàn)那樣會過于繁瑣，變考慮將上述步驟通過 SQL 中自定義函數(shù)來實(shí)現(xiàn)。先來復(fù)習(xí)下MySQL中的自定義函數(shù)。

自定義函數(shù)實(shí)例：

先來一個(gè)簡單的，創(chuàng)建一個(gè)函數(shù)將'2009-06-23 00:00:00'這樣格式的datetime時(shí)間轉(zhuǎn)化為'2009年6月23日0時(shí)0分0秒'這樣的格式：

DELIMITER $$
DROP?FUNCTION?IF?EXISTS?`sp_test`.`getdate`$$
CREATE?FUNCTION?`sp_test`.`getdate`(gdate datetime) RETURNS?varchar(255)
BEGIN
DECLARE?x VARCHAR(255) DEFAULT?'';
SET?x= date_format(gdate,'%Y年%m月%d日%h時(shí)%i分%s秒');
RETURN x;
END?$$
DELIMITER ;

解析：

第一句是定義一個(gè)結(jié)束標(biāo)識符，因?yàn)镸ySQL默認(rèn)是以分號作為SQL語句的結(jié)束符的，而函數(shù)體內(nèi)部要用到分號，所以會跟默認(rèn)的SQL結(jié)束符發(fā)生沖突，所以需要先定義一個(gè)其他的符號作為SQL的結(jié)束符；

第二句是如果這個(gè)函數(shù)已經(jīng)存在了，就刪除掉，sp_test是數(shù)據(jù)庫的名字，函數(shù)是跟數(shù)據(jù)庫相關(guān)聯(lián)的，getdate是函數(shù)的名字；

第三句是創(chuàng)建一個(gè)函數(shù)，()里是參數(shù)的名字和類型，RETURNS 定義這個(gè)函數(shù)返回值的類型；

函數(shù)體必須放在BEGIN END之間；

DECLARE 是定義函數(shù)體的變量，這里定義一個(gè)變量x,默認(rèn)是空，然后SET給x變量賦值；

RETURN 是返回值，這里把變量x返回，x的類型必須與第三句中定義的返回類型一致。

調(diào)用：

SELECT?getdate('2009-06-23 00：00：00');

返回 '2009年06月23日00時(shí)00分00秒'

六、最終答案

我們先來完成時(shí)間處理的自定義函數(shù)，代碼如下：

DELIMITER $$
DROP?FUNCTION?IF?EXISTS?func_date_sub $$
-- d1 6:00 d2 '2020-2-16 4:5:0'
CREATE?FUNCTION?func_date_sub(d1 VARCHAR(20),d2 DATETIME) RETURNS?INT
BEGIN
??IF?d2 IS?NULL?THEN
??RETURN?-1440;
??ELSE
??RETURN CEIL((UNIX_TIMESTAMP(CONCAT('2020-2-16 ',d1))-UNIX_TIMESTAMP(d2))/60);
??END?IF;
??END?$$
DELIMITER ;

SQL 查詢語句如下：

select?res.worker_name,res.attend,if(res.latetime<0,'是','否') as?isLate,if(
res.latetime<0,abs(res.latetime),0) as?latetime from
(select?ck.worker_name,ck.attend,func_date_sub(ck.sign_time,ck.st) latetime
from?(select?e.worker_name,e.sign_time,'2020-02-16'?as?attend,k.st from?
(select?* from?work_plan
where?datediff(end_date,'2020-2-16')>0?
or?end_date is?null) e
left?join
(select?worker_name,min(sign_time) st
from?sign_log group?by?worker_name) k
on?e.worker_name = k.worker_name) ck)res;

最終結(jié)果還是非常完美的，如果小伙伴有更好的意見，歡迎留言討論~

• 程序員值得收藏的精選11套后臺登錄頁面和管理頁面模版

• 50份優(yōu)秀Java求職者簡歷

• SpringCloud前后端分離實(shí)戰(zhàn)項(xiàng)目視頻教程分享

• 2020年全網(wǎng)最全BAT筆試面試題打包分享

感謝點(diǎn)贊支持下哈?

總結(jié)

以上是生活随笔為你收集整理的数据分析sql面试必会6题经典_师兄大厂面试遇到这条 SQL 数据分析题，差点含泪而归！...的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。